Select with priority - 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

SQL Server 2005 Forums

Transact-SQL (2005)

Select with priority

Author

Topic

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 06:09:59

Hello

I'm using a select on a view
Code :
SELECT
[ID]
,[Name]
,[ImgName]
,[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]

Each ID,Name can have different ImgName, ImgType

I need to filter the select to get only ONE id for any type but with a priority for the TypeValue

Is that possible ?

Thank for your help

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-22 : 06:25:52

Use Ranking function.

I am here to learn from Masters and help new bees in learning.

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-22 : 06:29:50

Is this you are looking for :

select [ID],[Name],[ImgName],[ImageType] from
(
Select
rank() over (Partition by [ImageType]) order by [TypeValue] desc as Srno,
[ID],
[Name],
[ImgName],
[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]
) as stab
where srno = 1

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 06:52:27

Thank you pk_bohra !

I'm just rying your suggestion

2 Points :
a) What is [TypeValue] in

rank() over (Partition by [ImageType]) order by [TypeValue] desc as Srno,

b) When trying your code in MS SQL server i get the message

quote:
The ranking function "rank" must have an ORDER BY clause.

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 07:07:05

Just change it to


rank() over (Partition by [ImageType] order by [TypeValue] desc) as Srno

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 08:02:45

You're a king

little point : it is ImageType : not TypeValue
rank() over (Partition by [ImageType] order by [ImageType] desc) as Srno

but can you explain a litle bit more about this syntax
Specialy

rank() over (Partition by [ImageType] order by [ImageType] asc) as
Srno,

and

where srno=1

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 08:55:37

Have a look here

http://weblogs.sqlteam.com/jeffs/archive/2007/03/28/60146.aspx

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 09:20:58

Hello PK_bohra and Idera

Your help is great.
I've made some investigation on Rank() and I understand the tip now

However, one problem remain
The rank method insure that I will get the first record of my order by clause
But is there a way to set a priory not based on a sort ?
Let says that I have a preference for ImageType=2

In group G0 I have ImageType 1,2,3,4 : 2 will be the one
In group G1 I have only ImageType 3 : 3 will be the one

How can I do that ?

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 10:17:51

you can just put 2 in the where condition.Something like this


select [ID],[Name],[ImgName],[ImageType] from 
(
Select 
rank() over (Partition by [ImageType] order by [ImageType] desc) as Srno,
[ID],
[Name],
[ImgName],
[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]
) as stab
where srno = 2

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 10:31:07

Hello
Thank you Idera

After some more investigation I'm just finding out that Rank does not work as expected

Look at the result of the query below !

select [ID],[Name],[TechnicalName],[ImgName],[ImageType],srno from 
(
Select 
rank() over (Partition by [ImageType] order by [ImageType] desc) as Srno,
[ID],
[Name],
[TechnicalName],
[ImgName],
[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]
) as stab
order by ID

Result :

ID	Name	TechnicalName	ImgName	ImageType	srno
A4A48F85-81E1-42C1-AFEE-00131E45D21A	Samsung A517	MOBPHONE	6CB52C90-6558-4324-8369-22118F94CB142328.jpg	1	1
A4A48F85-81E1-42C1-AFEE-00131E45D21A	Samsung A517	MOBPHONE	F4A401A6-10D0-4487-95B7-149E9DA086F72328.jpg	2	1

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 10:36:52

Put where condition.See the red part below



select [ID],[Name],[TechnicalName],[ImgName],[ImageType],srno from 
(
Select 
rank() over (Partition by [ImageType] order by [ImageType] desc) as Srno,
[ID],
[Name],
[TechnicalName],
[ImgName],
[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]
) as stab where srno = 2
order by ID

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 10:46:50

Hello Idera

Look at the values of Srno returned in my example
For my understanding it is not normal to get two 1 for two different imagetype !!

So where Srno = 2 will return nothing !!

Sachin.Nand

2937 Posts

Posted - 2010-06-22 : 10:56:19

Then use this


select [ID],[Name],[TechnicalName],[ImgName],[ImageType],srno from 
(
Select 
row_number()over (Partition by [ImageType] order by [ImageType] desc) as Srno,
[ID],
[Name],
[TechnicalName],
[ImgName],
[ImageType]
FROM [Touch-DEV].[dbo].[ViewYRDevices]
) as stab where srno = 2
order by ID

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-22 : 12:00:43

Thank a lot for your patience Idera

But the proposition to use row_number do not explain why rank() is not working

And in my example the idea to have where srno=2 will not work either because when 3 is the single ImageType srno will be 1

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 02:17:57

Olibara,
Please post some sample data and expected output.Without it,it's very difficult for us to understand what you need and we will be just going round in circles.

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-23 : 03:47:27

Hello Idera

Thanks for your help

Here is a sample of what I want to get
For each distinct ID I need to get ONE record with ImageType 2 as priority
If Type 2 is unavailable I take the first greater than 2 or the greatest if none is greater than 2

In the example below I've set in red the record to select

ID	TechnicalName	ImgName	ImageType
A4A48F85	MOBPHONE	22118F94CB142328.jpg	1
A4A48F85	MOBPHONE	149E9DA086F72325.jpg	2

ID	TechnicalName	ImgName	ImageType
A4A48F86	MOBPHONE	22118F94CB142326.jpg	1

ID	TechnicalName	ImgName	ImageType
A4A48F87	MOBPHONE	22118F94CB14232a.jpg	1
A4A48F87	MOBPHONE	149E9DA086F7232b.jpg	2
A4A48F87	MOBPHONE	149E9DA086F7232c.jpg	3

ID	TechnicalName	ImgName	ImageType
A4A48F87	MOBPHONE	NULL	0

Sachin.Nand

2937 Posts

Posted - 2010-06-23 : 04:09:09

[code]
declare @tbl as table(ID varchar(8),TechnicalName varchar(40),ImgName varchar(40),ImageType varchar)
insert into @tbl
select 'A4A48F85','MOBPHONE','22118F94CB142328.jpg','1' union all
select 'A4A48F85','MOBPHONE','149E9DA086F72325.jpg','2' union all
select'A4A48F86','MOBPHONE','22118F94CB142326.jpg','1' union all
select'A4A48F87','MOBPHONE','22118F94CB14232a.jpg','1' union all
select'A4A48F87','MOBPHONE','149E9DA086F7232b.jpg','2' union all
select'A4A48F87','MOBPHONE','149E9DA086F7232c.jpg','3' union all
select'A4A48F87','MOBPHONE',NULL,'0'

select * from @tbl

select distinct T.ID,T.ImageType,T.ImgName from @tbl t1

cross apply(select top 1 id,Imagetype,ImgName from @tbl t2 where t1.ID=t2.ID order by Imagetype desc)T

[/code]

Limitations live only in our minds. But if we use our imaginations, our possibilities become limitless.

PBUH

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 04:19:20

quote:
Originally posted by olibara

Hello Idera

Thanks for your help

Here is a sample of what I want to get
For each distinct ID I need to get ONE record with ImageType 2 as priority
If Type 2 is unavailable I take the first greater than 2 or the greatest if none is greater than 2

In the example below I've set in red the record to select

ID TechnicalName ImgName ImageType
A4A48F85 MOBPHONE 22118F94CB142328.jpg 1
A4A48F85 MOBPHONE 149E9DA086F72325.jpg 2

ID TechnicalName ImgName ImageType
A4A48F86 MOBPHONE 22118F94CB142326.jpg 1

ID TechnicalName ImgName ImageType
A4A48F87 MOBPHONE 22118F94CB14232a.jpg 1
A4A48F87 MOBPHONE 149E9DA086F7232b.jpg 2
A4A48F87 MOBPHONE 149E9DA086F7232c.jpg 3

ID TechnicalName ImgName ImageType
A4A48F87 MOBPHONE NULL 0

You wanted one record for each ID ? Why the record with NULL ImgName is also selected ?

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

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2010-06-23 : 04:28:43

assuming that the last line is a typo mistake, this should give you what you wanted. It is basically same as what Idera posted, the only different is the ORDER BY.



select	*
from	
(
	select	*, row_no = row_number() over (partition by ID order by case when ImageType = '2' then 1 else 2 end, ImageType)
	from	@tbl
) i
where	i.row_no	= 1

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

olibara
Yak Posting Veteran

94 Posts

Posted - 2010-06-23 : 04:39:27

Thank all

thank khtan
In fact the basic rule is to get one device for each ID
And for each device try to get the best image for the asked type
But if there is no image I have to select the device anyway

pk_bohra
Master Smack Fu Yak Hacker

1182 Posts

Posted - 2010-06-23 : 04:41:07

Hi khtan,

I admit that i didn't knew that we can use case statement in rank function also. I learned something today.

Edit: Thanks Khtan.

A small addition to your code:

quote:

If Type 2 is unavailable I take the first greater than 2 or the greatest if none is greater than 2

select *
from
(
select *, row_no = row_number() over (partition by ID order by case when ImageType = '2' then 1 else 2 end, ImageType desc)
from @t
) i
where i.row_no = 1

Regards,
Bohra

I am here to learn from Masters and help new bees in learning.

Next Page

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources