Problem with distinc/group by/nested select

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

General SQL Server Forums

New to SQL Server Programming

Problem with distinc/group by/nested select

Author

Topic

tiwas
Starting Member

37 Posts

Posted - 2010-02-10 : 02:53:15

Hi,

I have the following table:
[ID] [bigint] IDENTITY(1,1) NOT NULL,
[Agent_ID] [bigint] NOT NULL,
[Kunde_ID] [bigint] NULL,
[Timestamp] [datetime] NOT NULL CONSTRAINT [DF_Kundesupport_ID_Timestamp] DEFAULT (getdate()),
[Intension] [uniqueidentifier] NULL,
[pulse_id] [bigint] NULL,
CONSTRAINT [PK_Kundesupport_ID] PRIMARY KEY CLUSTERED

In this table, Kunde_ID can have duplicates (this is a log) and I need to return a list where only the newest entry for a Kunde_ID is selected.

The problem, as far as I can see:
* I cannot use distinct, as I would need to have all the selected columns in the clause. It somehow seems unlikely that that will ever happen as I have a timestamp in there.
* If I use group by Kunde_ID I need to have all the other columns in some sort of function - and I don't really see how I would do that. Espescially Intension, which is a string
* I tried setting up a nested select with a join, but that failed miserably...From memory, I think I did something like select * from kundesupport_id right join (select distinct kunde_id from kundesupport_id); - not that I think that's the right idea anyway...

Anyone care to share their insight?

Cheers!

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-02-10 : 02:59:43

[code]
SELECT t.*
FROM Yourtable t
INNER JOIN (SELECT Kunde_ID,MAX(ID) AS Recent
FROM YourTable
GROUP BY Kunde_ID) t1
ON t1.Kunde_ID = t.Kunde_ID
AND t1.Recent=t.ID
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP