T-SQL Question 1

[JayKusch]

Afternoon Folks ...

have a T-SQL script as follows ...


select sf.store_front_name,
sf.store_front_description,
i.image_location
from store_Fronts sf left join images i on
(sf.store_front_image_id=i.image_id)
where sf.store_front_id in
(256,107,1687,1376,2684,204,667,1945)


i would like to have the result set return the records in the order they were sent in the query itself. right now, the result set is ordered when it comes back (ie. 107,204, 256 ...) Any ideas?

Thanks

 

[cmmrfrds]

Perhaps a sub query would work, although it will probably display in the order it finds the ids in the table.

select
(select sf.store_front_name,
sf.store_front_description,
i.image_location
from store_Fronts sf left join images i on
(sf.store_front_image_id=i.image_id)
where sf.store_front_id = osf.store_front_id )
from store_Fronts osf
where osf.store_front_id in
(256,107,1687,1376,2684,204,667,1945)

 

[cmmrfrds]

This seems a little tedious but you could put a case statement in the order by clause to force an order.

select sf.store_front_name,
sf.store_front_description,
i.image_location
from store_Fronts sf left join images i on
(sf.store_front_image_id=i.image_id)
where sf.store_front_id in
(256,107,1687,1376,2684,204,667,1945)
order by CASE sf.store_front_id
when '256' then 1
when '107' then 2
when '1687' then 3
......
END

 

[rac2]

Maybe make a view for the basic query -

CREATE VIEW whole_store AS
select   sf.store_front_id,
         sf.store_front_name, 
         sf.store_front_description,   
         i.image_location
from     store_Fronts sf left join images i on 
        (sf.store_front_image_id=i.image_id)

Then

SELECT * FROM whole_store WHERE store_front_id = 256

UNION ALL

SELECT * FROM whole_store WHERE store_front_id = 107

UNION ALL

SELECT * FROM whole_store WHERE store_front_id = 1687

UNION ALL

SELECT * FROM whole_store WHERE store_front_id = 1376

UNION ALL

SELECT * FROM whole_store WHERE store_front_id = 2684

UNION ALL

etc.

 

[TheInsider]

Wouldn't the simplest method be to simply place the store_front_id numbers into a table in the order that you want them displayed? Then simply make an inner join between the new table and the sf table with no sorting defined? This would do 2 things: it would remove the need for the WHERE clause and it would provide the sorting that you require.

 

[JayKusch]

Here is a little more to chew on. we have a table w/ 20,000 records of merchants. we want to randomly grab a set of 10 records and then use that set of numbers in other queries. If a merchant has a high merchant_id, say 19,998, and he is the first one pulled on the random get, after then next query is run, he is no longer at the top record set due to SQL ordering them.

In otherwords if the random get pull ... 19998, 201, 5003, 19201 ect. when we pass that string of merchant ids to the next sql statement, it orders the result set to be ...

201, 5003, 19201, 19998

so this merchant is always at the bottom of the record set.

Thanks for all the great ideas and i will try the most recent one tomorrow and give a heads up on which one fits. please keep send in the reply though.