Filter query

Transact-SQL
1

hi,
need help with the following query, for the same origin and destiny,if there is a pricetype=3 show the row with the pricetype=3 else show the the row with pricetype=1
Example:
LineCode Origin Destiny Pricetype
2 606 618 3
2 606 618 1
2 607 618 1
3 606 618 1
3 607 618 1
3 606 618 3
Result:
LineCode Origin Destiny Pricetype
2 606 618 3
2 607 618 1
3 607 618 1
3 606 618 3

Thanks.

2

This is inefficient because it has to access the table three times but it works given the requirements. There is likely a better, more efficient way to do it.

/* Setup Sample Data Table */
DECLARE @Table TABLE
(
	LineCode TINYINT NOT NULL,
	Origin  SMALLINT NOT NULL,
	Destiny SMALLINT NOT NULL,
	Pricetype TINYINT NOT NULL,
	PRIMARY KEY(Origin, Destiny, LineCode, PriceType)
);

/* Insert into Sample Data Table */
INSERT INTO @Table (LineCode, Origin, Destiny, Pricetype)
SELECT x.LineCode, x.Origin, x.Destiny, x.Pricetype
  FROM 
	(
		VALUES 
		(2, 606, 618, 3),
		(2, 606, 618, 1),
		(2, 607, 618, 1),
		(3, 606, 618, 1),
		(3, 607, 618, 1),
		(3, 606, 618, 3)
	) AS x (LineCode, Origin, Destiny, Pricetype);

/* Get rows where PriceType = 3 */
SELECT *
  FROM @Table As t1
 WHERE t1.PriceType = 3
UNION ALL
/* Get rows where PriceType = 1 and not PriceType = 3 above */
SELECT *
  FROM @Table As t1
 WHERE NOT EXISTS
	( 
		SELECT *
		  FROM @Table As t2
		 WHERE t2.PriceType = 3
		   AND t1.Origin = t2.Origin
		   AND t1.Destiny = t2.Destiny
	)
  AND t1.Pricetype = 1
 ORDER BY 1, 2, 3;
3

It looks like LineCode needs to be included in the partitioning as well:

SELECT LineCode, Origin, Destiny, Pricetype
FROM (
    SELECT *, ROW_NUMBER() OVER(PARTITION BY Origin, Destiny, LineCode ORDER BY Pricetype DESC) AS row_num
    FROM @Table
) AS lines
WHERE row_num = 1
4

Taking a page from @bitsmed

 Select Top 1 With ties
        *
   From @Table
  Where Pricetype In (1, 3)
  Order By
        row_number() over(Partition By LineCode, Origin, Destiny 
                              Order By Pricetype desc)
5

I have to say that the TOP 1 WITH TIES is the most awesome thing I have seen with SQL Server in a long time.

6

Ooh, right, I should probably have used DENSE_RANK rather than ROW_NUMBER:

SELECT LineCode, Origin, Destiny, Pricetype
FROM (
    SELECT *, DENSE_RANK() OVER(PARTITION BY Origin, Destiny, LineCode ORDER BY Pricetype DESC) AS row_num
    FROM @Table
) AS lines
WHERE row_num = 1
1 Like
7

this one did it for me thanks so much all of you.