Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > comp.databases.ms-sqlserver > #971 > unrolled thread

Database query -- how to?

Back to article view | Back to comp.databases.ms-sqlserver

Contents

  Database query -- how to? Gerd Brix <gbrix@gmx.de> - 2012-04-12 01:02 -0700
    Re: Database query -- how to? bradbury9 <ray.bradbury9@gmail.com> - 2012-04-12 02:28 -0700
      Re: Database query -- how to? Gerd Brix <gbrix@gmx.de> - 2012-04-12 02:38 -0700
        Re: Database query -- how to? bradbury9 <ray.bradbury9@gmail.com> - 2012-04-12 04:10 -0700
    Re: Database query -- how to? Erland Sommarskog <esquel@sommarskog.se> - 2012-04-12 11:10 +0000
      Re: Database query -- how to? Gerd Brix <gbrix@gmx.de> - 2012-04-12 04:33 -0700

#971 — Database query -- how to?

Hi all,

I'm not too firm with writing SQL queries, thats why I'm asking here
for help.
There's a SQL Server Express with the following table:

ID     TIME
----------------
1       8:00
1       7:45
2       6:00
3       5:00
2       7:59

What I need to find out is the last time entered for all given IDs.
That means:
ID     TIME
----------------
1      8:00
2      7:59
3      5:00

Can someone please help me with this?

Thanks in advance!

-gerd

[toc] | [next] | [standalone]

#972

El jueves, 12 de abril de 2012 10:02:09 UTC+2, Gerd Brix  escribió:
> Hi all,
> 
> I'm not too firm with writing SQL queries, thats why I'm asking here
> for help.
> There's a SQL Server Express with the following table:
> 
> ID     TIME
> ----------------
> 1       8:00
> 1       7:45
> 2       6:00
> 3       5:00
> 2       7:59
> 
> What I need to find out is the last time entered for all given IDs.
> That means:
> ID     TIME
> ----------------
> 1      8:00
> 2      7:59
> 3      5:00
> 
> Can someone please help me with this?
> 
> Thanks in advance!
> 
> -gerd

In case the column TIME is DateTime type:
Select max(TIME) from TableName

If i is not DateTime, you will have to convert the data, heres a link to the documentation on Convert function and data types http://msdn.microsoft.com/es-es/library/ms187928.aspx

[toc] | [prev] | [next] | [standalone]

#973

Am Donnerstag, 12. April 2012 11:28:32 UTC+2 schrieb bradbury9:
> El jueves, 12 de abril de 2012 10:02:09 UTC+2, Gerd Brix  escribió:
[...]
> > There's a SQL Server Express with the following table:
> > 
> > ID     TIME
> > ----------------
> > 1       8:00
> > 1       7:45
> > 2       6:00
> > 3       5:00
> > 2       7:59
> > 
> > What I need to find out is the last time entered for all given IDs.
> > That means:
> > ID     TIME
> > ----------------
> > 1      8:00
> > 2      7:59
> > 3      5:00
> > 
> > Can someone please help me with this?
> > 
[...] 
> In case the column TIME is DateTime type:
> Select max(TIME) from TableName
> 
[...]

Not really :-(  This only delivers the latest entry. What I need is the latest entry for each ID. The example result shows this: 3 rows instead of only 1.

-gerd

[toc] | [prev] | [next] | [standalone]

#975

El jueves, 12 de abril de 2012 11:38:36 UTC+2, Gerd Brix  escribió:
> Am Donnerstag, 12. April 2012 11:28:32 UTC+2 schrieb bradbury9:
> > El jueves, 12 de abril de 2012 10:02:09 UTC+2, Gerd Brix  escribió:
> [...]
> > > There's a SQL Server Express with the following table:
> > > 
> > > ID     TIME
> > > ----------------
> > > 1       8:00
> > > 1       7:45
> > > 2       6:00
> > > 3       5:00
> > > 2       7:59
> > > 
> > > What I need to find out is the last time entered for all given IDs.
> > > That means:
> > > ID     TIME
> > > ----------------
> > > 1      8:00
> > > 2      7:59
> > > 3      5:00
> > > 
> > > Can someone please help me with this?
> > > 
> [...] 
> > In case the column TIME is DateTime type:
> > Select max(TIME) from TableName
> > 
> [...]
> 
> Not really :-(  This only delivers the latest entry. What I need is the latest entry for each ID. The example result shows this: 3 rows instead of only 1.
> 
> -gerd

select [id], max(time) as latestTime from tableName group by [id]

[toc] | [prev] | [next] | [standalone]

#974

Gerd Brix (gbrix@gmx.de) writes:
> There's a SQL Server Express with the following table:
> 
> ID     TIME
> ----------------
> 1       8:00
> 1       7:45
> 2       6:00
> 3       5:00
> 2       7:59
> 
> What I need to find out is the last time entered for all given IDs.
> That means:
> ID     TIME
> ----------------
> 1      8:00
> 2      7:59
> 3      5:00
> 
> Can someone please help me with this?
 
SELECT ID, MAX(Time) FROM tbl GROUP BY ID


-- 
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[toc] | [prev] | [next] | [standalone]

#976

Thanks so much!
Works like a charm. 

-gerd

[toc] | [prev] | [standalone]

Back to top | Article view | comp.databases.ms-sqlserver

csiph-web