Optimizing the data

Question

Post reply

Optimizing the data

deepika_goyal

Old Hand

Points: 375
More actions
October 4, 2012 at 1:11 am

#265390

Dear Alll,
I have one query, can anybody help in this..
I have one table and i need to get records from table on below mentioned cases.
My table is like :
No_PriorityItem Store
124434891000012024
124434891000022024
124534901000012024
124534901000022024
124534901000032024
Now i want data like :
If i have multiple Items for same Store then the item whose adjacent priority is lower i should select only that record.
Result should look like this :
No_PriorityItem Store
124434891000012024
124434891000022024
124534901000032024
please help i am not able to get this.
Regards,
Deepika

Viewing 13 posts - 1 through 13 (of 13 total)

You must be logged in to reply to this topic. Login to reply

deepika_goyal Old Hand Points: 375 More actions · Answer 1

deepika_goyal

Old Hand

Points: 375

October 4, 2012 at 1:32 am

#1545355

Can anybody help in this..???

durai nagarajan SSCoach Points: 15778 More actions · Answer 2

do you want to show one data irrespective of different priority right?

Regards
Durai Nagarajan

deepika_goyal Old Hand Points: 375 More actions · Answer 3

Hi,

I want to select those items whose priority is low in a combinatino of item and Store.

and if only one item present in the same store then that record should also fetch

deepika_goyal Old Hand Points: 375 More actions · Answer 4

deepika_goyal

Old Hand

Points: 375

October 4, 2012 at 1:41 am

#1545361

please help in this

durai nagarajan SSCoach Points: 15778 More actions · Answer 5

give us some time some body will help

Regards
Durai Nagarajan

deepika_goyal Old Hand Points: 375 More actions · Answer 6

I need it urgently..

i will be glad if somebody could be helpful....

durai nagarajan SSCoach Points: 15778 More actions · Answer 7

can you push this data to a temp table

select min(Priority) 'Priority', Item, Store

from tableName

group by Item, Store

and then join with main table to get No_ column

old idea but i cant think of better now.

Regards
Durai Nagarajan

deepika_goyal Old Hand Points: 375 More actions · Answer 8

Hi,

This i have already tried.

But after moving to temp table, i again have to join with my master table for the required fields.

Now challenge come when i have single Item present in store, in this case group by Store and Item will not give my required output.

and Priority column i am using for my comparison, my last output will have No_, Item, Store which have single item in the store with lowest priority..

Please help..i am not able to fetch the data.

Thanks a lot!!!

Regards,

Deepika

durai nagarajan SSCoach Points: 15778 More actions · Answer 9

[quote-0Now challenge come when i have single Item present in store, in this case group by Store and Item will not give my required output.

[/quote-0]

100003 is once in your example how min will not show this data by the above query , am i missing something or you are referring something.

Regards
Durai Nagarajan

Ken McKelvey SSCoach Points: 18939 More actions · Answer 10

Using the Windowed functions (ROW_NUMBER, RANK etc), is normally the easiest way to resolve this type of query.

-- *** Test Data ***

-- You should provide test data in this format so people can test it.

CREATE TABLE #t

(

No_ int NOT NULL

,Priority int NOT NULL

,Item int NOT NULL

,Store int NOT NULL

);

INSERT INTO #t

SELECT 1244, 3489, 100001, 2024

UNION ALL SELECT 1244, 3489, 100002, 2024

UNION ALL SELECT 1245, 3490, 100001, 2024

UNION ALL SELECT 1245, 3490, 100002, 2024

UNION ALL SELECT 1245, 3490, 100003, 2024;

-- *** Test Data ***

WITH OrderedItems

AS

(

SELECT No_, Priority, Item, Store

,ROW_NUMBER() OVER (PARTITION BY Store, Item ORDER BY Priority) AS rn

FROM #t

)

SELECT No_, Priority, Item, Store

FROM OrderedItems

WHERE rn = 1

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 11

This has to be homework because it's very simple.

Try ROW_NUMBER() OVER (PARTITION BY something ORDER BY somethingelse)

You will need to set it up as a CTE or derived table in order to filter by the values returned by ROW_NUMBER().

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

deepika_goyal Old Hand Points: 375 More actions · Answer 12

thanks a lot Ken!!!.

It worked...

Thanks once again 🙂