select 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

select query

Author

Topic

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-16 : 10:57:37

I have a table like
customerid address addressclass
101 India 2
101 UK 1
102 GER 2
103 USA 2

Default address class is 2. If a customerid has two addressclasses then I should select the address belong to addressclass '1'. The output should be

customerid address
101 UK
102 GER
103 USA

Please note that I have to select some other columns from other tables too.

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-16 : 11:39:38

what is the version of SQL Server you are using ?

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

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-16 : 12:25:05

2005

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-16 : 13:56:05

[code]
select customerid ,address
from
(
select row_number() over (partition by customerid order by addressclass asc) as Rn,*
from table
)t
where Rn=1
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

jassi.singh
Posting Yak Master

122 Posts

Posted - 2011-10-17 : 01:36:18

select *
from
(
select row_number() over (partition by customerid order by addressclass) as Sequence,*
from customer
)customertemp
where customertemp.Sequence=1

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 01:54:07

quote:
Originally posted by jassi.singh

select *
from
(
select row_number() over (partition by customerid order by addressclass) as Sequence,*
from customer
)customertemp
where customertemp.Sequence=1

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

How do you think this is different from what i posted a day ago?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Sachin.Nand

2937 Posts

Posted - 2011-10-17 : 03:38:04

quote:
Originally posted by visakh16

quote:
Originally posted by jassi.singh

select *
from
(
select row_number() over (partition by customerid order by addressclass) as Sequence,*
from customer
)customertemp
where customertemp.Sequence=1

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

How do you think this is different from what i posted a day ago?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well he has used a different table alias and the column name for the sequence generator..

PBUH

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 04:06:12

quote:
Originally posted by Sachin.Nand

quote:
Originally posted by visakh16

quote:
Originally posted by jassi.singh

select *
from
(
select row_number() over (partition by customerid order by addressclass) as Sequence,*
from customer
)customertemp
where customertemp.Sequence=1

Please mark answer as accepted if it helped you.

Thanks,
Jassi Singh

How do you think this is different from what i posted a day ago?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Well he has used a different table alias and the column name for the sequence generator..

PBUH

yeah thts a difference

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-17 : 05:14:04

Could anyone please write the code for sql 2000 too. I would also need to use in 2000 version too.
Many many thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-10-17 : 05:29:58

[code]
select t.customerid, t.address
from yourtable t
inner join
(
select customerid, addressclass = min(addressclass)
from yourtable
group by customerid
) m on t.customerid = m.customerid
and t.addressclass = m.addressclass
[/code]

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

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-17 : 08:57:22

I have written the query,

select t.*
from customeraddress t, (select customerid, addressclass = min(addressclass)
from customeraddress
group by customerid) m
where t.customerid = m.customerid
and t.addressclass = m.addressclass

The inner query is giving distinct rows and is fine. But when is joined with the outer query, it is also including some of the duplicates of addressclass=1

Can anyone help with this.
Many thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 09:01:41

do you mean you can have multiple records with same customerid and addressclass?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-17 : 09:11:06

I do not want to have multiple records with same customerid and addressclass.

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-17 : 09:17:16

Is there any way to prevent writing
select distinct(columnname1), columnname2,,,,,,,,,,,,, .
since there are so many columns in this table and I have to join to some other tables. so I have written select t.*

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 09:23:58

quote:
Originally posted by rajendrarama

Is there any way to prevent writing
select distinct(columnname1), columnname2,,,,,,,,,,,,, .
since there are so many columns in this table and I have to join to some other tables. so I have written select t.*

sorry you explanation doesnt make much sense

please post some sample data to clearly illustrate your problem and explain what you want.
For the data and output posted, Tan has already given you solution.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

rajendrarama
Starting Member

14 Posts

Posted - 2011-10-17 : 09:50:51

customerid address addressclass time
101 India 2 10:02
101 UK 1 10:03
101 FRA 1 10:04
102 GER 2 10:05
103 USA 2 10:06

For the above table,
when I run the query

select t.*
from customeraddress t, (select customerid, addressclass = min(addressclass)
from customeraddress
group by customerid) m
where t.customerid = m.customerid
and t.addressclass = m.addressclass

The output is like this:
101 UK 1 10:03
101 FRA 1 10:04
102 GER 2 10:05
103 USA 2 10:06

It has got one duplicate row.

The following code would prevent that by

select distinct t.customerid,t.address, t.addressclass
from customeraddress t, (select customerid, addressclass = min(addressclass)
from customeraddress
group by customerid) m
where t.customerid = m.customerid
and t.addressclass = m.addressclass

My question is:
There are so many columns to write after the distinct keyword. So rather than writing all the columns, is there any way we can make changes to

select t.*
from customeraddress t, (select customerid, addressclass = min(addressclass)
from customeraddress
group by customerid) m
where t.customerid = m.customerid
and t.addressclass = m.addressclass

to get the output
101 FRA 1 10:04
102 GER 2 10:05
103 USA 2 10:06

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-10-17 : 10:30:42

[code]select t.customerid, t.address
from yourtable t
inner join
(
select customerid, time = max(cast(time as datetime)),maxaddress1time = max(case when addressclass = 1 then cast(time as datetime) else null end)
from yourtable
group by customerid
) m on t.customerid = m.customerid
and t.time= coalesce(m.maxaddress1time ,m.time)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources