Help With SQL Select Query

boomers · 10-02-2010, 01:17 PM

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
????: NamePros.com http://www.namepros.com/programming/680193-help-with-sql-select-query.html
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.

defaultuser · 10-02-2010, 09:13 PM

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 · 10-03-2010, 11:54 AM

Heres the way I would do it!

Code:

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

defaultuser · 10-03-2010, 12:19 PM

Originally Posted by baxter

Heres the way I would do it!

Code:

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 :-)

Code:

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 · 10-04-2010, 07:03 AM

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

Code:

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:

Code:

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

which will give the same results.

defaultuser · 10-05-2010, 07:14 PM

Originally Posted by baxter

Code:

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.
????: NamePros.com http://www.namepros.com/showthread.php?t=680193

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

10-02-2010, 01:17 PM	THREAD STARTER #1 (permalink)
boomers NamePros Regular Join Date: Jun 2005 Posts: 518	Help With SQL Select Query 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 ????: NamePros.com http://www.namepros.com/programming/680193-help-with-sql-select-query.html 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. __________________ Free Horse Racing Tips

10-02-2010, 09:13 PM	#2 (permalink)
defaultuser Senior Member Join Date: May 2009 Location: internet@ctivist.com Posts: 4,790	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) __________________ A Member of: IdeationTeam.com HowToBeADomainer.com - a Domaining How To AuthorEditor.com Last edited by defaultuser; 10-02-2010 at 09:21 PM.

10-03-2010, 11:54 AM	#3 (permalink)
baxter NamePros Regular Join Date: Apr 2006 Posts: 360	Heres the way I would do it! Code: SELECT * FROM `test2` GROUP BY name ORDER BY FIND_IN_SET( result, 'Profit,Loss' ) __________________ Canadian Domain Registrar Ready.ca

10-04-2010, 07:03 AM	#5 (permalink)
baxter NamePros Regular Join Date: Apr 2006 Posts: 360	I setup a test database with his info and it outputs his required return: Code: 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: Code: SELECT * FROM `test2` GROUP BY name ORDER BY result DESC which will give the same results. __________________ Canadian Domain Registrar Ready.ca

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)