Click here to Skip to main content
11,930,048 members (44,303 online)
Rate this:
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 1:18am
digimanus 15-Jan-13 6:24am
In fact your determantion of tables is incorrect. Create A table City.
Suvabrata Roy 16-Jan-13 5:04am
which columns are required in your output ...?
Sumit_Kumar_Sinha 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 15-Jan-13 6:36am
hey....i want to select city name and last Inserted PID for each city ........
digimanus 15-Jan-13 6:57am
Do you need PID or NAME in the final result?
Sumit_Kumar_Sinha 15-Jan-13 9:34am
i need city and pid in the final result
digimanus 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 16-Jan-13 1:25am
it's throwing error :- Invalid column name 'City'.
digimanus 16-Jan-13 4:58am
your RIGHT. It is from Coupons in stead of From City
Sumit_Kumar_Sinha 17-Jan-13 0:16am
same error is coming....................
digimanus 17-Jan-13 3:50am
strange. I used the column names you gave in your question. What are the real columnnames?
Sumit_Kumar_Sinha 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 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 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 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
Top Experts
Last 24hrsThis month

Advertise | Privacy | Mobile
Web03 | 2.8.151126.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