how to use decending order use in sql

Question

0.00/5 (No votes)

See more:

i have using in this query..

how to use top 1 decending order in

pls help me any body

SQL

select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=1 union
select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,   b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=2   union
select  distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=3  union

select distinct  top 1  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=4 union
select distinct  top 1 a.product_price, a.product_id,  a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=5 union
select  distinct  top 1 a.product_price, a.product_id, a.product_name, b.category_id, b.category_name,a.small_image,a.product_image from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=6

Posted 23-Jun-15 0:01am

MohamedEliyas

Add a Solution

4 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CHill60 · Answer 1 · 2015-06-23T00:14:00

First of all get rid of all those unions - replace them with an IN clause[^]
Next - the DISTINCT needs to come after the TOP 1
Finally ORDER BY DESC[^] goes at the end.
If you were still using UNION the order by would still go right at the end. Your query becomes

SQL

select top 1 distinct  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image
from tbl_products_web as a
inner join tbl_category_web  as b on a.category_id=b.category_id
where b.category_id IN (1,2,3,4,5,6)
ORDER BY [whichever column here]

[EDIT - solution corrected based on OP comment]
There are a few ways you can achieve this

Option 1 - using Common Table Expressions

-- NOTE ORDER BY items must appear in the select list if SELECT DISTINCT is specified.
-- so the select from C1, C2 etc redefines the list of columns
with C1 as (select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated 
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=1 order by a.image_updated desc),
C2 as (select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,   b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=2 order by a.image_updated desc),
C3 as (select  distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=3 order by a.image_updated desc),
C4 as (select distinct  top 1  a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=4 order by a.image_updated desc),
C5 as (select distinct  top 1 a.product_price, a.product_id,  a.product_name,b.category_id,  b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=5 order by a.image_updated desc),
C6 as (select  distinct  top 1 a.product_price, a.product_id, a.product_name, b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated  
	from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=6 order by a.image_updated desc)
select product_price, product_id, product_name,category_id, category_name,small_image,product_image from C1
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C2
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C3
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C4
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C5
UNION
SELECT product_price, product_id, product_name,category_id, category_name,small_image,product_image FROM C6

Option 2 - using a temporary table and a loop

create table #temp
(
	product_price float,
	product_id int,
	product_name varchar(255),
	category_id int,
	category_name varchar(255),
	small_image varchar(max),
	product_image varchar(max),
	image_updated datetime
)

declare @i int = 1
while @i <= 6
begin
	insert into #temp select distinct  top 1 a.product_price, a.product_id, a.product_name,b.category_id, b.category_name,a.small_image,a.product_image,a.image_updated 
		from tbl_products_web as a inner join tbl_category_web  as b on a.category_id=b.category_id where b.category_id=@i order by a.image_updated desc
	set @i = @i + 1
end
select product_price, product_id, product_name,category_id, category_name,small_image,product_image 
from #temp

Jörgen Andersson · Answer 2 · 2015-06-23T20:15:00

If you are using SQL Server 2005 or newer you can use the analytic function RANK() to achieve what you want.
The best part is that it's generic and you don't need to know the categories in beforehand

SQL

WITH CTE AS (
    SELECT  a.product_price
           ,a.product_id
           ,a.product_name
           ,b.category_id
           ,b.category_name
           ,a.small_image
           ,a.product_image
           ,RANK() OVER (PARTITION BY b.category_id ORDER BY a.image_updated DESC) AS Rank
    FROM    tbl_products_web as a 
    INNER JOIN tbl_category_web as b ON a.category_id = b.category_id
    )
SELECT  a.product_price
       ,a.product_id
       ,a.product_name
       ,b.category_id
       ,b.category_name
       ,a.small_image
       ,a.product_image
FROM    CTE
WHERE   Rank = 1

Note that if you have two images with the same last image_updated for a category you'll get both with this query.
If this is a problem you can exchange the RANK() for a ROW_NUMBER().
But you won't know which of the row that get selected.

OriginalGriff · Answer 3 · 2015-06-23T00:11:00

Solution 1

SQL

SELECT DISTINCT TOP 1...
FROM ...

ORDER BY MyColumn DESC

Posted 23-Jun-15 0:11am

OriginalGriff

anand vemuri · Answer 4 · 2015-06-23T00:28:00

Solution 3

SQL

SELECT DISTINCT  TOP 1 * FROM TBL_PRODUCTS_WEB ORDER BY sortColumn  DESC

//see here
http://sqlfiddle.com/#!15/d3e5e/12[^]

Posted 23-Jun-15 0:28am

anand vemuri

how to use decending order use in sql

4 solutions

Solution 2

Solution 4

Solution 1

Solution 3

Add your solution here

Preview 0