sort record by use

Question

sort record by use

abhas

SSCarpal Tunnel

Points: 4681
More actions
August 5, 2014 at 4:54 am

#309720

Hi All,
Please help me on below scenario:
I need to write SP which will call from front End to populate dropdown, But in dropdown uppermost value should be frequently used.
For example, My SP Select CountryID,Country Columns where i am displaying Country in dropdown. Now first time it will sort by ascending, next time if user selects US then US should be top and rest things after that.
Now If user selects Canada 3 times then result should sort as Canada,US, and rest countries.
if again US Selects 7 times then result should display as US,Canada,........
In short need to sort depending used previously. i.e. most popular first and so on......
Please help.
Thanks,
Abhas.

Viewing 7 posts - 1 through 6 (of 6 total)

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

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

First thing you'll need a table that stores the user's last selection. Each user should have one row per drop down you want to do this to. When they select the drop down, you'll need to update their last selection.

Then, in the query, you join in that table, filter on the user and do a multi-column sort, probably with a case statement.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 2

Thanks Gail,

I will try. Could you please share sample code if you have?

Thanks,

Abhas.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

I don't, and I'm way too busy to write the whole thing, unless you want to wait until October.

Have a go at it, I've given you the main elements. If you get stuck, post back

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

abhas SSCarpal Tunnel Points: 4681 More actions · Answer 4

Thanks Gail,

I am trying to do. Think will stuck to multi sort using probably case statement.

Thanks

Abhas.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

That's the easy part, and the last part. You need to get all the stuff around storing their last few selections done and working first.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Eric M Russell SSC Guru Points: 125562 More actions · Answer 6

One approach is to creating your sorted country list is to query whatever transactional table would contain country code. For performance to be acceptable, it's essential that whatever columns you're referencing to be covered by an index. You'll also want to consider caching and re-using the result each time the user loads the application.

For example, let's assume we have two tables; one containing (5) country codes and another containing sales totals by period for (2) countries.

create table Country (country_code char(2) not null primary key);

insert into Country ( country_code)

values ('US'),('CA'),('BR'),('CN'),('MX');

create table Period_Sales

(primary key (period_code, country_code)

, period_code char(7) not null

, country_code char(3) not null

, sales_units int not null );

create index ix_country_sales on Period_Sales ( country_code, sales_units );

insert into Period_Sales ( period_code, country_code, sales_units ) values

('2014/01', 'US', 130456),('2014/02', 'US', 145112),('2014/03', 'US', 132882),

('2014/01', 'CA', 122520),('2014/02', 'CA', 123965);

This query will return a list of all (5) counries; the first (2) countries with sales sorted by total sales, and then the remaining (3) countries without sales in order by county code.

select Country.country_code, Sales.sales_units

from Country

left join

(

select country_code, sum( sales_units )sales_units

from Period_Sales

group by country_code

) Sales on Sales.country_code = Country.country_code

order by Sales.sales_units desc, Country.country_code;

country_code sales_units

US 408450

CA 246485

BR NULL

CN NULL

MX NULL

Another variation of the query will return the list of countries ranked first by record count and then by country code.

select Country.country_code, Sales.period_count

from Country

left join

(

select country_code, count(period_code) period_count

from Period_Sales

group by country_code

) Sales on Sales.country_code = Country.country_code

order by Sales.period_count desc, Country.country_code;

country_code period_count

US 3

CA 2

BR NULL

CN NULL

MX NULL

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho