Select distinct, order by not-distinct

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 2005 Forums

Transact-SQL (2005)

Select distinct, order by not-distinct

Author

Topic

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-11 : 08:25:54

This is probably a classic:

I have a table with id's and dates.
so lets say:

ID Date
1 1-1-2007
1 2-1-2007
2 3-1-2007

And I want it to return DISTINCT id's which are ordered by Date, so it should return:

1
2

Perhaps this an be done by a GROUP BY or a subquery, can anyone help.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 08:35:00

What's the use of ordering by Date when you don't want Date as a part of output?

declare @t table
(
	ID int,
	dt datetime
)

insert @t
select 1, '20070101' union all
select 1, '20070102' union all
select 2, '20070103'

select distinct id
from (select top 100 percent id from @t order by dt) x

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-11 : 08:42:48

I did

SELECT DISTINCT VisitorID

FROM
(select top 100 percent VisitorID from dbo.Visitors order by VisitDate)

But it gives incorrect syntax near the keyword 'order'
(you probably think I'm stupid now)

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2007-04-11 : 08:52:04

quote:
Originally posted by trouble2

I did

SELECT DISTINCT VisitorID

FROM
(select top 100 percent VisitorID from dbo.Visitors order by VisitDate) as Tmp

But it gives incorrect syntax near the keyword 'order'
(you probably think I'm stupid now)

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 12:10:23

quote:
Originally posted by harsh_athalye

quote:
Originally posted by trouble2

I did

SELECT DISTINCT VisitorID

FROM
(select top 100 percent VisitorID from dbo.Visitors order by VisitDate) as Tmp

That won't give you the results you want in SQL 2005. In order for SQL to allow you to have an ORDER BY on an inline or derived table it requires that you also include TOP or FOR XML. However, by specifying TOP SQL 2005 seem to ignore the ORDER BY clause.

EDIT: Hum, I'm loosing my mind today.. I'm getting strange results.

I expanded the data set a little:

declare @t table
(
	ID int,
	dt datetime
)

insert @t
select 5, '20060101' union all
select 5, '20070101' union all
select 4, '20050101' union all
select 4, '20060101' union all
select 6, '20060102' union all
select 6, '20060102' union all
select 2, '20050103'

select DISTINCT id
from (select top 100 percent id from @t order by dt) x

-- vs
select id
from (select top 100 percent id from @t order by dt) x

Obviously, differnt queries, but it appears the DISTINCT is ordering by ID. YMMV.

I better get some coffee or something...

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2007-04-11 : 12:54:54

I think some cofee may have helped, try this:

SELECT id
FROM
(
	SELECT 
		id,
		dt,
		RANK() OVER (PARTITION BY id ORDER BY dt) as Rank
	FROM @t
) AS Derived
WHERE Rank = 1
ORDER BY dt DESC

-Ryan

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-12 : 10:42:31

Wow, that seems to work!!!

I don't know why it all has to be that difficult though...

Should this work in SQL 2000 to?

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-18 : 09:49:13

Hmmmmm, I tried to implement the sp to a sql 2000 server, but it said it didn't understand RANK.

Any suggestions

pootle_flump

1064 Posts

Posted - 2007-04-18 : 09:56:59

????

declare @t table
(
	ID int,
	dt datetime
)

insert @t
select 5, '20060101' union all
select 5, '20070101' union all
select 4, '20050101' union all
select 4, '20060101' union all
select 6, '20060102' union all
select 6, '20060102' union all
select 2, '20050103'

select id
from @t 
GROUP by id
order by max(dt) asc

You don't mention which dates you want to use if an id has more than one (i.e. do you want to order by the earliest or latest date).

EDIT - typo

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-18 : 09:59:53

I don't mind that much, as long as there is some ordering going on.
They will probably not differ that much (max 1 day)

pootle_flump

1064 Posts

Posted - 2007-04-18 : 10:05:04

Lol. Well - a difference is a difference whether a day, milli second or millenia. My query orders by the latest date for each id. If you don't mind then I guess it is fine

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-18 : 10:19:13

Eeeehrrm, actually, the whole idea was that it would also return the date column.

this:

SELECT id
FROM
(
SELECT
id,
dt,
RANK() OVER (PARTITION BY id ORDER BY dt) as Rank
FROM @t
) AS Derived
WHERE Rank = 1
ORDER BY dt DESC

Works fine in SQL 2005, only it doesn't work for SQL 2000

pootle_flump

1064 Posts

Posted - 2007-04-18 : 10:47:53

Eeeeehrm - never said that before (check out your first post - ids only) and your 2005 query doesn't either. What result would you expect from Lamprey's data? Stick the MAX(dt) bit into the select clause if you just want the last date per id.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-04-18 : 10:54:04

quote:
Originally posted by pootle_flump

Eeeeehrm - never said that before (check out your first post - ids only) and your 2005 query doesn't either. What result would you expect from Lamprey's data? Stick the MAX(dt) bit into the select clause if you just want the last date per id.

Yes, well, ok then, I quoted some stuff, but what I am actually using is:

SELECT
WWVisitorID,
WWDate
FROM
(
Select WWVisitorID, WWDate, RANK() OVER (Partition BY WWVisitorIDorder by WWDate) As Rank
From SomeTable
)
As Derived
WHERE Rank = 1
Order By WWDate Desc

pootle_flump

1064 Posts

Posted - 2007-04-18 : 11:09:25

2k AND 2k5 equivelent to the rank function.

declare @t table
(
	ID int,
	dt datetime
)

insert @t
select 5, '20060101' union all
select 5, '20070101' union all
select 4, '20050101' union all
select 4, '20060101' union all
select 6, '20060102' union all
select 6, '20060102' union all
select 2, '20050103'

select id
, min(dt)
from @t 
group by id
order by min(dt) desc

Melody
Starting Member

1 Post

Posted - 2007-05-01 : 16:24:20

Trouble2 - Did you ever get your answer for 2K? I am having the same issue. My ORIGINAL sql is:

SELECT DISTINCT(O.SU_INV_ITEM_ID)
, O.DATE
, O.PO_ID
, O.REQ_ID
, O.VENDOR_PO
, I.DESCR40
, O.SU_ORDER_STS_FLG
, O.SU_QTY_ORDERED
, O.SU_COST
, O.SU_TOTAL_COST
, O.OPRID
FROM PS_SU_SS_ORDER O
, PS_SU_SS_INVENTORY I
WHERE O.SU_INV_ITEM_ID = I.SU_INV_ITEM_ID
AND O.SU_ORDER_STS_FLG IN ('C', 'X')
ORDER BY O.SU_INV_ITEM_ID, O.DATE DESC

But i get the message about needing to use TOP if I have an order by. I can't seem to get the syntax correct.

trouble2
Constraint Violating Yak Guru

267 Posts

Posted - 2007-05-02 : 10:54:49

CREATE PROCEDURE [dbo].[GetDistinctVisitorID]
AS

SELECT
VisitorID,
min(Date) as Date
FROM
Visitors
Group by
VisitorID
Order by Min(Date) desc

GO

WORKS FOR ME

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources