Click here to Skip to main content
11,803,019 members (46,787 online)
Rate this: bad
Please Sign up or sign in to vote.
See more: SQL Server
able Product

PID(PK) --- Name --- ImagUrl

1 - Mobile --- something
2 - Laptop --- something
3 - Fashion --- something

Tabe Coupons


1 - 2 - Bangalore
2 - 3 - Pune
3 - 2 - Mumbai
4 - 1 - Bangalore
5 - 2 - Mumbai

I want to select distinct City name records joining two tables
(if i have five different city then i want to select one one record from each city).
Pl'z suggest me, how can i fetch this records.
I trired using distinct keyword to fetch records but i am unable to find desire output....
Posted 15-Jan-13 0:18am
digimanus at 15-Jan-13 6:24am
In fact your determantion of tables is incorrect. Create A table City.
Suvabrata Roy at 16-Jan-13 5:04am
which columns are required in your output ...?
Sumit_Kumar_Sinha at 17-Jan-13 0:15am
for distinct city PID and Name

1 solution

Rate this: bad
Please Sign up or sign in to vote.

Solution 1

select distinct Sumit.City, Sumit.Name  -- or Sumit.PID, depends on what you want to show
    select Row_Number() over(Partition By c.ID, c.City Order BY c.ID desc, City) as rowno,
	c.ID, c.City, p.Name, p.PID
    From Coupons  c
    JOIN Product p on =
) as Sumit
where Sumit.rowno = 1

1 assumption is that you use Sql Server by Microsoft
Sumit_Kumar_Sinha at 15-Jan-13 6:36am
hey....i want to select city name and last Inserted PID for each city ........
digimanus at 15-Jan-13 6:57am
Do you need PID or NAME in the final result?
Sumit_Kumar_Sinha at 15-Jan-13 9:34am
i need city and pid in the final result
digimanus at 15-Jan-13 9:42am
the query above is already changed. in stead of sumit.Name you stat sumit.PID. Have you tested the query?
Sumit_Kumar_Sinha at 16-Jan-13 1:25am
it's throwing error :- Invalid column name 'City'.
digimanus at 16-Jan-13 4:58am
your RIGHT. It is from Coupons in stead of From City
Sumit_Kumar_Sinha at 17-Jan-13 0:16am
same error is coming....................
digimanus at 17-Jan-13 3:50am
strange. I used the column names you gave in your question. What are the real columnnames?
Sumit_Kumar_Sinha at 21-Jan-13 2:12am
with CTE as
select CouponsStock.CouponsID, ProdSubCatItemsDescription.Items_Desc_ShortDescription,
ROW_NUMBER() over(PARTITION BY CouponsStock.CouponsID, ProdSubCatItemsDescription.Items_Desc_ShortDescription order by ProdSubCatItemsDescription.Items_Desc_ShortDescription )as Cnt
from category inner join ProductCategory on
inner join ProductSubCategory on ProductCategory.Prod_Cat_ID=ProductSubCategory.Prod_Cat_ID
inner join ProductSubCatItmes on ProductSubCategory.Prod_Sub_Cat_ID=ProductSubCatItmes.Prod_Sub_Cat_ID
inner join ProdSubCatItemsDescription on ProductSubCatItmes.Prod_Sub_Cat_Items_ID=ProdSubCatItemsDescription.Prod_Sub_Cat_Items_ID
inner join CouponsStock on ProdSubCatItemsDescription.Prod_Sub_Cat_Items_Desc_ID=CouponsStock.Prod_Sub_Cat_Items_Desc_ID
where category.Cat_Name='Fashion'
and ProdSubCatItemsDescription.Items_Desc_Status<>'Disabled'
and CouponsStock.CouponsEndDate>=GETDATE()
and CouponsStock.CouponsStatus='Visible'
select CouponsID, Items_Desc_ShortDescription
from CTE where Cnt=1
Sumit_Kumar_Sinha at 21-Jan-13 2:13am
16 KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )
17 KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )
39 Crosscreek Fil-A-Fil Casual Shirt
46 Live Life Colorful : Western Wear.....
47 50% Off For Select Time Period
Sumit_Kumar_Sinha at 21-Jan-13 2:14am
the above is output........i want to select the distinct reocrds because "KRIAA - Men's Handloom Embroidery Short Kurti ( TTMK 4011 )" title repeated two times in the output
digimanus at 21-Jan-13 6:38am
In your CTE no columns with name City are defined, that is why my query fails. Add city to your CTE or see that your Row_number function lead to unique 'distinct' rows

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

  Print Answers RSS
0 OriginalGriff 335
1 KrunalRohit 291
2 F-ES Sitecore 270
3 CPallini 270
4 Sergey Alexandrovich Kryukov 234
0 OriginalGriff 2,950
1 Maciej Los 1,910
2 KrunalRohit 1,862
3 CPallini 1,735
4 Richard MacCutchan 1,157

Advertise | Privacy | Mobile
Web02 | 2.8.151002.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100