CODEWITHSUNDEEP
sql
jac
jac

Reputation: 9726

Select All Columns From Table With 1 Distinct Column and a Where Condition

This had been asked multiple times, but I am not finding a solution that works for me. I have a view similar to below. I need to run a query that returns all of the columns, but only once for any unique GLASS_ID. I also need a where clause WHERE GLASS_ID LIKE '%' + @PartialGlassId + '%'

VEH_ID   GLASS_ID   OPENING_SEQ  PART_NUM  PREFIX_CD ANT_FLAG  BLK_SIZE1
26975     DB00201        1          201       DB        Y        14.00
26864     DB00375        1          375       DB        N        16.00
26865     DB00375        1          375       DB        N        16.00
26866     DB00375        1          375       DB        N        16.00
38929     DB00408        1          408       DB        N        12.00
38930     DB00408        1          408       DB        N        12.00
38931     DB00408        1          408       DB        N        12.00
38932     DB00408        1          408       DB        N        12.00
38933     DB00408        1          408       DB        N        12.00
38990     DB00408        1          408       DB        N        12.00
38991     DW01015        1         1015       DB        N        12.00

The results should be 

VEH_ID  NAGS_GLASS_ID OPENING_SEQ PART_NUM PREFIX_CD ANT_FLAG BLK_SIZE1
26975     DB00201        1          201       DB        Y        14.00
26864     DB00375        1          375       DB        N        16.00
38929     DB00408        1          408       DB        N        12.00
for WHERE GLASS_ID LIKE '%DB00%'

Upvotes: 1

Views: 1755

Answers (2)

xQbert
xQbert

Reputation: 35333

Select distinct max(veh_ID), glass_ID, Opening_SEQ, part_Num, PRefix_CD, Ant_Flag, BLK_Size1
FROM yourTable
WHERE GLASS_ID LIKE '%' + @PartialGlassId + '%'
Group by glass_ID, Opening_SEQ, part_Num, PRefix_CD, Ant_Flag, BLK_Size1

other option 

Select distinct group_Concat(veh_ID, ', '), glass_ID, Opening_SEQ, part_Num, PRefix_CD, Ant_Flag, BLK_Size1
FROM yourTable
WHERE GLASS_ID LIKE '%' + @PartialGlassId + '%'
Group by glass_ID, Opening_SEQ, part_Num, PRefix_CD, Ant_Flag, BLK_Size1

Upvotes: 0

Raphaël Althaus
Raphaël Althaus

Reputation: 60493

SELECT b.* 
FROM yourTable b
INNER JOIN
  (SELECT MIN(VEH_ID) as minV,  GLASS_ID as g
   FROM yourTable
   GROUP BY GLASS_ID) as d
  ON b.Glass_ID = d.g and b.VEH_ID = d.minV
WHERE b.GLASS_ID LIKE  '%DB00%'

NOTE : the WHERE clause, in this case, could be in the subquery from the INNER JOIN also.

Upvotes: 1

Related Questions