Help With SQL Select Query

[boomers]

First of all the table and data:

Time Date Name Result

13:00 01 Jan 2010 Name01 Profit
13:30 01 Jan 2010 Name02 Profit
13:30 01 Jan 2010 Name02 Loss
14:30 01 Jan 2010 Name03 Loss

What I want to do is display these results, however for the record that is duplicated (Name02) I only want to show the 'Profit' row.

So the query would end up with the following:

13:00 01 Jan 2010 Name01 Profit
13:30 01 Jan 2010 Name02 Profit
14:30 01 Jan 2010 Name03 Loss

The end result is to be displayed within a DataList (dont know if it can be omitted after the SQL query). Would appreciate any help.

 

[DU]

If it's a word Profit / Loss then this selects all the Profit and all the Loss that aren't profit. There are other ways to do this.. this is one.

Select Time, Date, Name, Result
FROM TABLE
WHERE
RESULT = ‘Profit’
UNION

Select Time, Date, Name, Result
FROM TABLE A
WHERE
Result = ‘Loss’
AND NOT EXISTS
(Select 1
FROM TABLE B
WHERE RESULT=‘Profit’
A.Date = B.Date and
A.Name = B.Name)

 

[baxter]

Heres the way I would do it!

SELECT *
FROM `test2`
GROUP BY name
ORDER BY FIND_IN_SET( result, 'Profit,Loss' )

 

[DU]

Heres the way I would do it!

SELECT *
FROM `test2`
GROUP BY name
ORDER BY FIND_IN_SET( result, 'Profit,Loss' )

Does that even work?

You have select/group by mismatch.

FIND_IN_SET is a nice function but only in MySQL

SELECT *
FROM
(
SELECT name, SUM(FIND_IN_SET(result, 'profit,loss')) AS P_L
FROM table
WHERE 
GROUP BY name
) AS A
WHERE P_L IN (1,2)

Might work (3 is both)

 

[baxter]

I setup a test database with his info and it outputs his required return:

Edit   	Delete  13:00 	01 Jan 2010 	Name01 	Profit
Edit 	Delete 	13:30 	01 Jan 2010 	Name02 	Profit
Edit 	Delete 	14:30 	01 Jan 2010 	Name03 	Loss

Alternatively if your not using mysql and Profit and Loss are the only two words for the result you could use:

SELECT *
FROM `test2`
GROUP BY name
ORDER BY result DESC

which will give the same results.

 

[DU]

SELECT *
FROM `test2`
GROUP BY name
ORDER BY result DESC

which will give the same results.

Only if you run MySQL in non ANSI mode, I suppose.

I'm not sure if I hate or like this but I usually stick with something closer to ANSI because it might need to work in DB2/SQL Server.

But I guess I learned something new today! Rep Pts 4 U