Selecting distinct depending on a single column

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Selecting distinct depending on a single column

Author

Topic

peterkh
Starting Member

4 Posts

Posted - 2006-07-04 : 00:16:32

Hi Folks

Basically i want to get back rows depending on a single column value. At the moment im querying for various values and a date_submitted field. The problem is that there are multiple equal entries for the date_submitted field (but not the other fields) and because this data is to be graphed only one of those rows is used/useful.

So to try and clear up that confusing mess, no matter whether the other field values are distinct or not, i want to only return one row with the value say 10-12-2006 7:00. So if there are rows like the following i want to only return one of them, not really concerned which one

Current Voltage Date_Submitted
3 54 10-12-2006 7:00
5 77 10-12-2006 7:00

If i use distinct in the usual fashion i get both rows because they are distinct

Needing help

Thanks in advance

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 00:24:01

try this

select	*
from	table	t
where	t.[Current] in (select top 1 [Current] from table x where x.Date_Submitted = t.Date_Submitted)

peterkh
Starting Member

4 Posts

Posted - 2006-07-04 : 00:42:46

Didnt quite work. Thanks for the quick response khtan. Im assuming that table x can be the same as table t in the where clause ?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 00:52:19

Yes. table x in the subquery is the same as table t as in the main query.

Is this what you want ?

declare @table table
(
	[Current] 		int,
	Voltage 		int,
	Date_Submitted		datetime
)
insert into @table
select	3, 54, '10-12-2006 7:00'	union all
select	5, 77, '10-12-2006 7:00'	union all
select	6, 50, '10-12-2006 5:00'	union all
select	8, 51, '10-12-2006 5:00'	

select	*
from	@table	t
where	t.[Current] in (select top 1 [Current] from @table x where x.Date_Submitted = t.Date_Submitted)

/* RESULT
Current     Voltage     Date_Submitted                                         
----------- ----------- ------------------------------------------------------ 
3           54          2006-10-12 07:00:00.000
6           50          2006-10-12 05:00:00.000
*/

peterkh
Starting Member

4 Posts

Posted - 2006-07-04 : 01:32:39

Yes - That is exactly what i am after. It just didnt work in my database. Obviously it is my fault.
The only other problem is that i need it to return a distinct date_submitted row even any of the other fields are null

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-07-04 : 02:20:18

"The only other problem is that i need it to return a distinct date_submitted row even any of the other fields are null"
Care to explain a bit more on this ? Or perhaps with some sample data ?

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-04 : 04:59:52

quote:
Originally posted by peterkh

Yes - That is exactly what i am after. It just didnt work in my database. Obviously it is my fault.
The only other problem is that i need it to return a distinct date_submitted row even any of the other fields are null

If NULL values are your main problem, then assuming that you are not concerned about which Current & Voltage value you want, following simple SQL can help you:

select max([current]), max(voltage), Date_Submitted
from @table
group by Date_Submitted

Even if table contains NULL values for other fields this will work.
Is this what you want?

Harsh Athalye
India.
"Nothing is Impossible"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-07-04 : 05:02:33

quote:
Originally posted by harsh_athalye

quote:
Originally posted by peterkh

Yes - That is exactly what i am after. It just didnt work in my database. Obviously it is my fault.
The only other problem is that i need it to return a distinct date_submitted row even any of the other fields are null

If NULL values are your main problem, then assuming that you are not concerned about which Current & Voltage value you want, following simple SQL can help you:

select max([current]), max(voltage), Date_Submitted
from @table
group by Date_Submitted

Even if table contains NULL values for other fields this will work.
Is this what you want?

Harsh Athalye
India.
"Nothing is Impossible"

Oh...and I forgot to add IsNull if you want to handle Null values also:

select max(IsNull([current],0)), max(IsNull(voltage,0)), Date_Submitted
from @table
group by Date_Submitted

Harsh Athalye
India.
"Nothing is Impossible"

peterkh
Starting Member

4 Posts

Posted - 2006-07-04 : 16:49:33

Thank you both khtan and harsh_athalye for your help. I think what you suggested will work for me thank you harsh.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources