Prev: How to check record exists before INSERT multiple records
Next: convert to stored proc
From: Denis on 22 Jun 2010 17:12
I have following table from which I need to retrieve the unique value.
computername username machine_id stime
name1 T0000 1 2010-05-20
name2 T0000 1 2010-05-21
name2 T0000 2 2010-05-22

I would like to retrieve as unique record including all fields
computername username machine_id stime
name2 T0000 2 2010-05-22

I know i can do group by on username and max(stime) but then I don't have
the other fields.

any ideas are welcome.



--
Denis G
From: Eric Isaacs on 22 Jun 2010 17:27
I don't think you explained yourself well or gave a good example since
the example you provided could yield the results you want by just
doing a MAX() on each column...

SELECT
MAX(computername) AS computername,
MAX(username) AS username,
MAX(machine_id) AS machine_id,
MAX(stime ) as stime
FROM
tablename

-Eric Isaacs
From: Erland Sommarskog on 22 Jun 2010 18:07
Denis (Denis(a)discussions.microsoft.com) writes:
> I have following table from which I need to retrieve the unique value.
> computername username machine_id stime
> name1 T0000 1 2010-05-20
> name2 T0000 1 2010-05-21
> name2 T0000 2 2010-05-22
>
> I would like to retrieve as unique record including all fields
> computername username machine_id stime
> name2 T0000 2 2010-05-22
>
> I know i can do group by on username and max(stime) but then I don't have
> the other fields.
>
> any ideas are welcome.

WITH numbered AS (
SELECT computername, username, machine_id, stime,
rowno = row_number() OVER (PARTITION BY username
ORDER BY stime DESC)
FROM tbl
)
SELECT computername, username, machine_id, stime
FROM tbl
WHERE rowno = 1

This solution requires SQL 2005 or later. Please always specify which
version of SQL Server you are using.


--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: Wojciech Garwol on 23 Jun 2010 02:58
"Denis" wrote:
> I have following table from which I need to retrieve the unique value.
> computername username machine_id stime
> name1 T0000 1 2010-05-20
> name2 T0000 1 2010-05-21
> name2 T0000 2 2010-05-22
>
> I would like to retrieve as unique record including all fields
> computername username machine_id stime
> name2 T0000 2 2010-05-22

If I understood the problem correctly, the solution would be

select top 1 * from aTable order by stime desc

Regards, Wojciech Garwol
 | 
Pages: 1
Prev: How to check record exists before INSERT multiple records
Next: convert to stored proc