writting query.. - 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

writting query..

Author

Topic

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-14 : 02:35:10

Hello friends,
I am using Ms sql server 2005 for my application..
I have a table like

Id MobNum ResId Status Date
1 919999999999 1234 Fail 14/02/09
2 919999999999 1234 Del 14/03/09
3 919999999999 3456 Del 14/04/09
4 919595959595 2345 Del 14/05/09

I want to check only MobNum and ResId combination for selecting data If the combination have multiple rows then select any single row from those multiple rows... so output should like..

Id MobNum ResId Status Date
1 919999999999 1234 Fail 14/02/09
3 919999999999 3456 Del 14/04/09
4 919595959595 2345 Del 14/05/09

I mean selected data should contain single MobNum and ResId combination..

How to write query for this???
plz help me ..
its really urgent
Thanking you in advance...

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-14 : 02:49:54

create table ashish2(Id int identity(1,1) ,MobNum varchar(50) ,ResId varchar(50), Status varchar(50) ,Date varchar(50))
insert into ashish2
select '919999999999' ,'1234' ,'Fail' ,'14/02/09' union all
select '919999999999' ,'1234' ,'Del' ,'14/03/09' union all
select '919999999999' ,'3456' ,'Del' ,'14/04/09' union all
select '919595959595' ,'2345' ,'Del' ,'14/05/09'

select id,mobnum,resid,status,date from
(select *,row_number() over (partition by Mobnum,Resid order by RESid desc) as Rn from ashish2)t
where t.rn=1 order by id

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-14 : 03:14:26

Hi ashish,
Thank you very much..
Your solution work for me..
Thanks again....
have a nice day..

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-14 : 03:22:01

hello Ashish
there is one problem I did not able to create view for your query because of 'over' keyword can u tell me how to create view for above query...
Thanking you in advance...

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-14 : 03:43:26

To use the new windowed functions in SQL Server 2005 and later, you have to set your database's compatibility level to 90 or more.

E 12°55'05.63"
N 56°04'39.26"

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-17 : 05:46:33

hello friends,
I have table 'tblTest' like

Id Code description date
1 123 des1 5/5/09
2 121 des2 5/8/09
3 123 des1 5/8/08

I want the query similar to

select * from tbltest group by Code, description

plz help me its really urgent
Thanking you in advance...

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 05:56:12

Not quite clear what you want. Please explain in detail.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-17 : 06:00:36

quote:
Originally posted by harsh_athalye

Not quite clear what you want. Please explain in detail.

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want distinct rows from above table on the basis of Code and description. Code and description combination should not repeat in resultset.

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 06:02:45

In that case, which date should be considered for output?

For code - 123 and description - des1, we have two dates:

5/5/09
5/8/08

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-17 : 06:04:01

quote:
Originally posted by harsh_athalye

In that case, which date should be considered for output?

For code - 123 and description - des1, we have two dates:

5/5/09
5/8/08

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

you can take any one of them

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 06:10:35

I assume maximum date of the two:

Select code, description, max([date]) as [date]
from tblFoo
Group by code, description

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

amol_benare604
Starting Member

8 Posts

Posted - 2009-03-17 : 06:15:06

quote:
Originally posted by harsh_athalye

I assume maximum date of the two:

Select code, description, max([date]) as [date]
from tblFoo
Group by code, description

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

I want all columns to display....

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2009-03-17 : 06:20:13

What about the ID column? The problem which is there for date column, also applies there. Which of the ID to be considered for output?

Harsh Athalye
India.
"The IMPOSSIBLE is often UNTRIED"

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-03-17 : 06:29:25

are u looking for this

select id,code,description,date from
( select * ,row_number() over( partition by code,description order by id ) as rn from @temp ) t
where t.rn = 1

ashishashish
Constraint Violating Yak Guru

408 Posts

Posted - 2009-03-21 : 07:47:06

May Be like this....

select max(sid) as id,max(date) as date,code,description from ashish3 group by code,description

Thanks......

jcb267
Constraint Violating Yak Guru

291 Posts

Posted - 2009-03-21 : 11:27:48

quote:
Originally posted by amol_benare604

hello Ashish
there is one problem I did not able to create view for your query because of 'over' keyword can u tell me how to create view for above query...
Thanking you in advance...

Are you using sql 2008?

If you are using reserved keywords in your query for purposes other than keywords (e.g. column names), you will need to put them in brackets - like [over].

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources