Select within a select - help needed please

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

Transact-SQL (2008)

Select within a select - help needed please

Author

Topic

billbutler16
Starting Member

3 Posts

Posted - 2010-12-08 : 11:29:55

Hi All

I need help with a query, based on the data below I'll try to explain.


UserId  ActivationDateTimeGMT	Group		ActivationFlag	HasQueueFlag	I3TimeStampGMT
------  ---------------------   -----   	--------------  ------------    --------------
sce5	2010-12-01 12:36:31	UKTeam_002	0		0		2010-12-01 12:36:31	
sce5	2010-12-02 15:44:11	UKTeam_002	0		0		2010-12-02 15:44:10	
sce5	2010-12-03 12:33:04	UKCoach	0	0				2010-12-03 12:33:04	
sce5	2010-12-06 13:27:21	UKCoach	0	0				2010-12-06 13:27:20	*
sea5	2010-12-01 12:17:10	UKTeam_003	0		0		2010-12-01 12:17:09	
sea5	2010-12-02 15:44:36	UKTeam_003	0		0		2010-12-02 15:44:36	*
sei1	2010-12-01 12:36:31	UKTeam_002	0		0		2010-12-01 12:36:31	
sei1	2010-12-02 15:44:11	UKTeam_002	0		0		2010-12-02 15:44:10	
sei1	2010-12-03 12:33:04	UKCoach	0	0				2010-12-03 12:33:04	*
ste5	2010-12-01 12:06:20	UKTeam_004	0		0		2010-12-01 12:06:20	
ste5	2010-12-02 15:44:59	UKTeam_004	0		0		2010-12-02 15:44:58	*
test	2010-12-08 12:26:57	UKTeam_001	0		0		2010-12-08 12:26:56	*

..sorry about the formatting - if I find out how to put tabs in, I'll re-post.

Basically, I have a table which records all the 'Groups' a person has belonged to. Their existing team is obviously their 'latest' entry - as timestamped by the last column.

For example UserID 'sce5' has had 4 amendments to their Group - I would just like to extract the latest one (I have put an asterix by the records I would like to return in a query).

Where there is only one entry I would like to return that one record.

I have tried to get my head around Select[Field1,Field2] From[Table]WHERE(Select Max(ActivationDateTimeGMT)) etc..etc...

but I need help - can anyone please show me how??

Thanks in advance

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:39:17

"if I find out how to put tabs in, I'll re-post"

You can re-edit your post (rather than re-post) by pressing Edit Reply button

You need to put [CODE] ... [/CODE] around your "code"

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:43:12

[code]SELECT [T_RowNumber],
UserId, ActivationDateTimeGMT, Group, ActivationFlag, HasQueueFlag, I3TimeStampGMT
FROM
(
SELECT [T_RowNumber] = ROW_NUMBER()
OVER
(
PARTITION BY UserId
ORDER BY UserId, I3TimeStampGMT DESC
),
X.*
FROM
(
SELECT UserId, ActivationDateTimeGMT, Group, ActivationFlag, HasQueueFlag, I3TimeStampGMT
FROM YourTable
WHERE ...
) AS X
) AS T
WHERE T_RowNumber <= 1
ORDER BY I3TimeStampGMT DESC
[/code]
Untested!

billbutler16
Starting Member

3 Posts

Posted - 2010-12-08 : 11:44:16

Thanks Kristen, it's still a bit wonky - but I'll try again later if this isn't acceptable.

Kristen
Test

22859 Posts

Posted - 2010-12-08 : 11:49:24

Looks fine to me

Your message crossed with my suggested solution (just in case you didn't spot it - this forum isn't the best when messages cross)

billbutler16
Starting Member

3 Posts

Posted - 2010-12-08 : 11:54:34

Great !! :)

The code worked 1st time after me putting in the table name and the WHERE in the sub-SELECT.

Indebted to you Kristen , thank you very much

jcelko
Esteemed SQL Purist

547 Posts

Posted - 2010-12-10 : 16:22:57

Please post DDL, so that people do not have to guess what the keys, constraints, Declarative Referential Integrity, data types, etc. in your schema are. Please learn to follow ISO-11179 data element naming conventions and formatting rules. Temporal data should use ISO-8601 formats. Code should be in Standard SQL as much as possible and not local dialect.

We don't even know the table name. Try not to use BIT flags in SQL; that was assembly language and has no place in RDBMS. GROUP is reserved word in SQL and too vague to be a data element name anyway.

Her is a gu8ess without DDL:

SELECT X.*
FROM (SELECT user_id, activation_datetimegmt, something_grp,
activation_flag, has_queue_flag, i3_timestampgmt,
MAX(i3_timestampgmt) OVER (PARTITION BY user_id)
AS i3_timestampgmt_max
FROM Nameless_Void) AS X
WHERE X.i3_timestampgmt = i3_timestampgmt_max;

Unlike the ROW_NUMBER() solution, this does not require sorting

--CELKO--
Books in Celko Series for Morgan-Kaufmann Publishing
Analytics and OLAP in SQL
Data and Databases: Concepts in Practice
Data, Measurements and Standards in SQL
SQL for Smarties
SQL Programming Style
SQL Puzzles and Answers
Thinking in Sets
Trees and Hierarchies in SQL

Kristen
Test

22859 Posts

Posted - 2010-12-11 : 03:02:21

"Try not to use BIT flags in SQL"

What's the alternative? Waste a Byte when a Bit would do?

Sachin.Nand

2937 Posts

Posted - 2010-12-11 : 06:34:46

quote:
...MAX(i3_timestampgmt) OVER (PARTITION BY user_id)

This kind of queries causes unecessary overhead as it uses TableSpools which can be expensive if used againts large amount of data.

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources