CODEWITHSUNDEEP
sql
bilal
bilal

Reputation: 21

Select min value from Tables using (sub Select )

I am trying to select a specific row from the tables. All are good, but the last select min is not working. So I need the min Leg Number after I make the whole selection.

SELECT cashier.* , legs.* ,cashier.id as cashier, 
cashier.cashierNumber as cashierNum ,cashier.fullName as cashier
FROM myTable
INNER JOIN  legs ON main.main= legs.legMain 
INNER JOIN  cashier ON legs.cashier = cashier.id 
WHERE legs.RRZZFrom ='RR' 
AND legs.LegNumber = (SELECT Min(legs.LegNumber) FROM legs)

Upvotes: 0

Views: 66

Answers (4)

gotqn
gotqn

Reputation: 43636

Try this:

WITH DataSource AS
(
    SELECT cashier.* 
          ,legs.* 
          ,cashier.id as cashier
          ,cashier.cashierNumber as cashierNum 
          ,cashier.fullName as cashier
          ,MIN(legs.LegNumber) OVER() AS [MinLegNUmber]
    FROM myTable
    INNER JOIN  legs 
        ON main.main= legs.legMain 
    INNER JOIN  cashier 
        ON legs.cashier = cashier.id 
    WHERE legs.RRZZFrom ='RR' 
)
SELECT *
FROM DataSource
WHERE LegNumber = [MinLegNUmber];

The idea is to use a OVER() clause to calculate the minimum value after the selection for each row:

MIN(legs.LegNumber) OVER()

Then in outer query to return only the rows which are matching this value.

The OVER clause is particular powerful syntax which allows to perform operations (ranking, aggregations) over given set of values. The OVER() syntax means the whole entity.

Upvotes: 0

Greg Low
Greg Low

Reputation: 1586

Are you looking for the MIN leg number only from the selected data? In that case, something like this:

WITH Details
AS
(
    SELECT cashier.* , legs.* ,cashier.id as cashier, 
    cashier.cashierNumber as cashierNum ,cashier.fullName as cashier
    FROM myTable
    INNER JOIN  legs ON main.main= legs.legMain 
    INNER JOIN  cashier ON legs.cashier = cashier.id 
    WHERE legs.RRZZFrom ='RR' 
)
SELECT d.*
FROM Details AS d
WHERE d.LegNumber = (SELECT MIN(d2.LegNumber) FROM Details AS d2);

Not sure if you'd need to alias any other columns there, as I don't know the table layout.

Upvotes: 0

Ed Bangga
Ed Bangga

Reputation: 13006

you might want to try using cte.

with cte as (
  select min(LegNumber) as minLegNum from legs
)SELECT cashier.* , legs.* ,cashier.id as cashier, 
    cashier.cashierNumber as cashierNum ,cashier.fullName as cashier
    FROM myTable
    INNER JOIN  legs ON main.main= legs.legMain 
    INNER JOIN  cashier ON legs.cashier = cashier.id 
    INNER JOIN cte c on c.minLegNum = legs.LegNumber
    WHERE legs.RRZZFrom ='RR'

Upvotes: 0

Tony
Tony

Reputation: 910

  where legs.RRZZFrom ='RR' and legs.LegNumber in (select min(legs.LegNumber) from legs)

Upvotes: 1

Related Questions