Another Query question... - 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

Another Query question...

Author

Topic

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-10 : 15:29:31

Ugh... what am I missing?

I have this table:


Juris_mvt_seq_num   Cos        clm    Juris_mvt_typ
       4           001257     001257      R
       5           001257     001257      T

When I run this query I get both I only want the max in the Juris_mvt_seq_num column


select max(juris_mvt_seq_num) as juris_mvt_seq_num, [cos], clm, juris_mvt_typ 
 From test1

group by [cos], juris_mvt_typ, clm

What am I doing wrong????

Here's the table info thanks!


CREATE TABLE [dbo].[test1](
[JURIS_MVT_SEQ_NUM] [smallint] NOT NULL,
[COS] [char](6) NOT NULL,
[CLM] [char](6) NOT NULL,
	[JURIS_MVT_TYP] [char](1) NOT NULL,
	[test1] [int] IDENTITY(1,1) NOT NULL)


Insert into test1

Select '4', '001257', '001257', 'R' union all
Select '5', '001257', '001257', 'T'

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2011-05-10 : 15:33:06

If you just want one row, then use TOP 1.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2011-05-10 : 15:40:54

If you use Tara's suggestion then also include an ORDER BY clause and remove the group by and max:


select top 1 juris_mvt_seq_num, [cos], clm, juris_mvt_typ 
from test1 
order by juris_mvt_seq_num desc

Be One with the Optimizer
TG

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-10 : 19:56:56

Thanks so much Tkizer and TG. What's the difference between Top and Max?

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-05-10 : 20:17:08

Top and Max are totally different.

Top (n) act on the result of the query and return first n number of rows of the result. Top should always use together with ORDER BY.

Max (column) is an aggregate function. It gives the maximum / biggest value of that column

KH
[spoiler]Time is always against us[/spoiler]

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 07:47:40

Okay thanks for the explanation now I see Top won't work for me as I need to get the biggest value of the column.

I would like this table:


Juris_Mvt_seq_num     COS       CLM    Juris_mvt_TYP
     4                001257   001257      R
     5                001257   001257      T
     1                005637   568147      R 
     2                005637   568147      T
     3                005637   568147      R
     1                242138   242138      R
     2                242138   242138      T
     1                279785   460121      R 
     2                279785   460121      T
     3                279785   460121      R

To look like this:


Juris_Mvt_seq_num         COS       CLM    Juris_mvt_TYP
       5                001257   001257      T
       3                005637   568147      R
       2                242138   242138      T
       3                279785   460121      R

How can I get that by using Max?

CREATE TABLE [dbo].[test1](
[JURIS_MVT_SEQ_NUM] [smallint] NOT NULL,
[COS] [char](6) NOT NULL,
[CLM] [char](6) NOT NULL,
[JURIS_MVT_TYP] [char](1) NOT NULL,
[test1] [int] IDENTITY(1,1) NOT NULL)

insert into test1

Select '1', '005637', '568147', 'R' union all
Select '2', '005637', '568147', 'T' union all
Select '3', '005637', '568147', 'R' union all
Select '1', '242138', '242138', 'T' union all
Select '2', '242138', '242138', 'R' union all
Select '1', '279785', '460121', 'T' union all
Select '2', '279785', '460121', 'R' union all
Select '3', '279785', '460121', 'T'

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 08:06:14

Your sample data doesn't match your expected output, but perhaps this?
SELECT *
FROM
(
select Juris_Mvt_seq_num ,[cos] ,clm,Juris_mvt_TYP
,[row] = row_number() over(partition by [cos] ,clm order by Juris_Mvt_seq_num desc)
from test1
) a

WHERE row = 1

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 08:27:04

Thanks Jim! This works perfectly! Can you please explain the query so I can understand what you had done?

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 08:31:59

One last question...I need to join another table to get the doc field. The table I want to join is called T2 and it can be joined on the cos fields that's in both tables.

join T2 on test.cos=T2.cos

Where would I join that in the query you produced?

Thanks!

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-05-11 : 08:32:23

The trick is in the row_number() , also see rank() dense_rank() and ntile(), that assigns a number to a row based on the partition clause, which determines your groups, and the order by clause that says how to number them. I said order by
Juris_Mvt_seq_num desc which means that the biggest one got assigned 1, the second biggest 2, etc. You can run the inner part by itself and play around with it -- add or remove columns from the partition, get rid of the partition by, etc.'

Jim

Everyday I learn something that somebody else already knew

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 08:50:44

Thanks. Running the inner query is perfect I now see what's happening!


5	001257	001257	T	1
4	001257	001257	R	2
3	005637	568147	R	1
2	005637	568147	T	2
1	005637	568147	R	3
2	242138	242138	R	1
1	242138	242138	T	2
3	279785	460121	T	1
2	279785	460121	R	2
1	279785	460121	T	3

Perfect!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 09:24:11


SELECT *
FROM
(
select Juris_Mvt_seq_num ,[cos] ,clm,Juris_mvt_TYP
,[row] = row_number() over(partition by [cos] ,clm order by Juris_Mvt_seq_num desc)
from test1
) a

WHERE row = 1

Thanks!

JJ297
Aged Yak Warrior

940 Posts

Posted - 2011-05-11 : 10:59:23

Figured it out...


SELECT t.doc, isnull(count(t.cossn),0) as PendInPSC, a.cossn, a.clmssn
FROM

(select Juris_Mvt_seq_num ,[cos] ,clms,Juris_mvt_TYP, org_id
,[row] = row_number() over(partition by [cos] ,clms order by Juris_Mvt_seq_num desc)
from t2 j
) a
left join
t2dibpend t on a.cos=t.cos
WHERE row = 1 and juris_mvt_typ='r' and org_id like 'p%' and dib_mvt_seq_num is not null
group by t.doc, a.cos, a.clms

Thanks again for your help!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources