Select Statement - SQL Server Forums

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

Select Statement

Author

Topic

dr223
Constraint Violating Yak Guru

444 Posts

Posted - 2013-09-13 : 07:54:50

Hi,

I have the follwoing select statement which extracts for a prac_no with a respective max (col_uid)

select prac_no,  max(col_uid) as max_col_uid from gprdsql.TblCollections where stage = 'Processed'
group by prac_no
order by prac_no

Results

Prac_no max_col_uid
1 100
2 879
3 566

Now, I wanted to add an additional field and re-wrote the query as;


select prac_no, col_date, max(col_uid) as max_col_uid from gprdsql.TblCollections where stage = 'Processed'
group by prac_no, col_date
order by prac_no

Results

Prac_no col_date max_col_uid
1 12/12/2008 98
1 13/12/2008 97
1 14/12/2008 100
2 15/12/2008 866
2 16/12/2008 879
3 17/12/2008 565
3 18/12/2008 566

I want my result to be;

Prac_no col_date max_col_uid
1 14/12/2008 100
2 16/12/2008 879
3 18/12/2008 566

Please ny help...

Thank you

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 08:01:13

this?

select prac_no, max(col_date) as col_date, max(col_uid) as max_col_uid from gprdsql.TblCollections where stage = 'Processed'
group by prac_no
order by prac_no

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 08:21:14

--May be this?
SELECT t1.prac_no, t2.max_col_date, MAX(t1.col_uid) max_col_uid
FROM gprdsql.TblCollections t1
JOIN (select prac_no, max(col_date) as max_col_date from gprdsql.TblCollections where stage = 'Processed' group by prac_no ) t2
ON t1.prac_no = t2.prac_no AND t1.col_date = t2.max_col_date
where t1.stage = 'Processed'
GROUP BY t1.prac_no, t2.max_col_date

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 08:26:02

quote:
Originally posted by bandi

--May be this?
SELECT t1.prac_no, t2.max_col_date, MAX(t1.col_uid) max_col_uid
FROM gprdsql.TblCollections t1
JOIN (select prac_no, max(col_date) as max_col_date from gprdsql.TblCollections where stage = 'Processed' group by prac_no ) t2
ON t1.prac_no = t2.prac_no AND t1.col_date = t2.max_col_date
where t1.stage = 'Processed'
GROUP BY t1.prac_no, t2.max_col_date

--
Chandu

Hello Chandu,
isn't it so that my solution with group by and max() selects the same value?
Not sure...

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 08:32:55

Prac_no col_date max_col_uid
1 12/12/2008 98
1 13/12/2008 97
1 14/12/2008 100
1 13/12/2008 101

For the above sample data your query will return 1 14/12/2008 101
whereas my query will return 1 14/12/2008 100

OP has to decide on this scenario which will fit user's need...
It depends on how data reside in table...

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 08:42:21

101? there is no 101...

Too old to Rock'n'Roll too young to die.

bandi
Master Smack Fu Yak Hacker

2242 Posts

Posted - 2013-09-13 : 08:52:03

Yes there is no 101 in the sample data.. I'm talking about actual data...
There might be that kind of data

--
Chandu

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-09-13 : 08:54:26