Click here to Skip to main content
Rate this: bad
good
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
 
ID PID City
 
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
Comments
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
good
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
from
(
    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 c.pid = p.pid
) as Sumit
where Sumit.rowno = 1
 
1 assumption is that you use Sql Server by Microsoft
  Permalink  
v4
Comments
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
category.Cat_ID=ProductCategory.Cat_ID
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)



Advertise | Privacy | Mobile
Web02 | 2.8.141220.1 | Last Updated 16 Jan 2013
Copyright © CodeProject, 1999-2014
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