Query with value1

Question

My view table is like this:

position price orderid buylog otherlog
1         15   100     08:00  08:01
2         15   100     08:00  08:02
2         15   100     08:00  08:05
2         15   100     08:00  08:02
2         15   101     08:10  08:15
2         15   101     08:10  08:12
2         15   102     08:20  08:25
2         15   103     08:30  08:31
2         15   103     08:30  08:32
2         15   103     08:30  08:33

The expected result:

position price orderid buylog otherlog
1         15   100     08:00  08:01
2         15   100     08:00  08:05
2         15   101     08:10  08:15
2         15   102     08:20  08:25
2         15   103     08:30  08:33

This is just a part-step to get going I guess, what I actually want is:

position price
1         15 
2         60

So I need an overview of total payments per position.

But for now, I need is: for every position and orderid, I want just the row with the highest otherlog entry.

Now there were otherlog-times that were lower than the buylog-times, but I got them filtered out by simply buylog < otherlog.

But now I don't know how to exactly show only the highest otherlog from every orderid-group. I tried with max(otherlog), but it still outputs the first table.

This is a view after joining together three tables, and I would like to get the expected result in the very same query.

The query is like this:

select position,price,orderid,buylog,otherlog 
from table1 inner join table2 on t1.userid=t2.userid  
inner join table3 on t2.id=t2.id 
where (some conditions to narrow down the results)

I'm using ms sql server 2012.

//edit

The query:

Use [dbname]

go

    with cte
    as  (   select olt.position,
                ot.price,
                ot.orderid,
                ot.buylog = min(ot.buylog) over (partition by olt.position,ot.orderid),
                olt.otherlog = max(olt.otherlog) over (partition by olt.position,ot.orderid),
                rn=row_number() over(partition by olt.position, order by olt.position)
            from ordertable as ot inner join anothertable as at 
                            on ordertable.userid=anothertable.userid

                inner join otherlogtable as olt on anothertable.id=otherlogtable.sessionlogid
        )
    select  
    olt.position,
    ot.price,
    ot.orderid,
    ot.buylog,
    olt.otherlog
    from
    cte
    where
    rn=1

if i manage to get to the second table, yes. but getting to the second table that i posted already gives me problems. — msa, Mar 06 '13 at 09:28

Tim Schmelter · Answer 1 · 2013-03-06T12:05:48.663

0

This should work (not sure with the origin of your columns though):

WITH cte 
     AS (SELECT position, 
                price, 
                orderid, 
                buylog = Min(buylog) 
                          OVER( 
                            partition BY position,orderid), 
                otherlog = Max(otherlog) 
                            OVER( 
                              partition BY position,orderid), 
                rn = Row_number() 
                     OVER( 
                       partition BY position,orderid 
                       ORDER BY position) 
         FROM  T1)
SELECT position, 
       price, 
       orderid, 
       buylog, 
       otherlog 
FROM   cte 
WHERE  rn = 1

Here's a DEMO

Edit So here is the complete query with your joins, to take your edited question into account:

WITH cte 
     AS (SELECT olt.position, 
                ot.price, 
                ot.orderid, 
                ot.buylog = Min(ot.buylog) 
                              OVER ( 
                                partition BY olt.position, ot.orderid), 
                olt.otherlog = Max(olt.otherlog) 
                                 OVER ( 
                                   partition BY olt.position, ot.orderid), 
                rn=Row_number() 
                     OVER( 
                       partition BY olt.position 
                       ORDER BY olt.position) 
         FROM   ordertable AS ot 
                INNER JOIN anothertable AS at 
                        ON ordertable.userid = anothertable.userid 
                INNER JOIN otherlogtable AS olt 
                        ON anothertable.id = otherlogtable.sessionlogid) 
SELECT position, 
       price, 
       orderid, 
       buylog, 
       otherlog 
FROM   cte 
WHERE  rn = 1

The problem was:

remove the table names from the outer select on the CTE (e.g. SELECT position instead of SELECT olt.position)
remove the comma between the PARTITION BY part of the ROW_NUMBER and the ORDER BY: rn=Row_number() OVER( partition BY olt.position ORDER BY olt.position)

edited Mar 06 '13 at 12:05

answered Mar 06 '13 at 09:29

Tim Schmelter

450,073
74
686
939

1

wow, i never worked with cte's and over() and row-number... xD i'll try to adapt this to my actual tables. thank you! – msa Mar 06 '13 at 09:33
hey @tims, i managed to remove all errors, but one still remains: Msg 102, Level 15, State 1, Line 7 Incorrect syntax near '='. line 7 is "as (select position,", right after "with cte". what could be the problem here? – msa Mar 06 '13 at 10:07
@msa: Maybe you have something before the cte, then you need to teminate your previous statement with `;`, you can start it in this way: `;WITH CTE AS ...`. http://stackoverflow.com/a/710697/284240 – Tim Schmelter Mar 06 '13 at 10:33
doesnt work either... before the "with cte", i only have "use [dbname] go". directly after "go" is the "with cte"... – msa Mar 06 '13 at 10:46
@msa: Difficult to see without your query. You might want to edit your question. – Tim Schmelter Mar 06 '13 at 10:48
its identical to your query. i only adjusted the table- and column-names to the actual ones in my db. – msa Mar 06 '13 at 10:53
@msa: I have no idea. Have a look at the sql-fiddle here with your sample data: http://www.sqlfiddle.com/#!3/37eff/3/0 Note that i had forgotten to include the `position` column into the `PARTITION BY`. Edited my answer (but that could not be the reason for your error). – Tim Schmelter Mar 06 '13 at 10:58
it works in the fiddle... damn. in sql server, it underscores the = sign between "buylog = Min(buylog)" with a red line, but none of the other = signs. and yeah, the position did not fix the error. btw i'm using table aliases in the from clause of the cte and i'm referencing to columns using these aliases - could that cause problems? – msa Mar 06 '13 at 11:08
oh and in the from-clause of the cte, i'm using aliases for the tables i'm joining and i'm referencing columns using these aliases. could that cause problems? – msa Mar 06 '13 at 11:14
@msa: No, you can use the same sql query inside a cte which you could use outside. Does the query work without the CTE? The same error if you use `... AS buylog, ...`? (so the other way to provide a column alias) – Tim Schmelter Mar 06 '13 at 11:16
yes, without cte the query works and outputs the first table from my starting post. "as buylog" didnt help either :( – msa Mar 06 '13 at 11:24
@msa: Then provide your query in your question, there must be a difference. – Tim Schmelter Mar 06 '13 at 11:24
added the query in the question. thanks for your help and patience. – msa Mar 06 '13 at 11:38
Remove aliases from lower select - these are not valid in this context. You're performing select over cte, not original aliases. – Arvo Mar 06 '13 at 11:49
@tims: been there, done that, but its still the same error message "Incorrect syntax near '='". – msa Mar 06 '13 at 11:52
@msa: Two things, remove the table names from the outer select on nthe `CTE` (e.g. `SELECT position` instead of `SELECT olt.position`) and remove the comma between the `PARTITION BY` part of the `ROW_NUMBER` and the `ORDER BY`: `rn=Row_number() OVER( partition BY olt.position ORDER BY olt.position)`. – Tim Schmelter Mar 06 '13 at 12:03
i removed the aliases form the outer select - the problem is, 2 columns of them (buylog, otherlog) actually have the very same name. if i just use the column name, i get "invalid column name". using aliases results in "The multi-part identifier could not be bound." – msa Mar 06 '13 at 12:05
@msa: I don't understand why `buylog, otherlog` are the same names. Just use an alias in the CTE as i've done above. – Tim Schmelter Mar 06 '13 at 12:45
@tims: they just happen to have the same name - both log different times. i need to use additional conditions and doing this in the outer select gives me errors. doing it in the cte-select does too. – msa Mar 06 '13 at 13:22
It's getting too abstract. You can select the columns from the cte in the select, you can also add aditional conditions. Are you sure that this issue is still the same as this question? Maybe you need to provide another question which targets the problem and shows all necessary details. – Tim Schmelter Mar 06 '13 at 13:26

Ravi Singh · Answer 2 · 2013-03-07T11:50:46.810

0

Try this:

with cte

as  (   select olt.position,

            ot.price,

            ot.orderid,

            log1 = min(ot.buylog) over (partition by olt.position,ot.orderid),

            log2 = max(olt.otherlog) over (partition by olt.position,ot.orderid),

            rn=row_number() over(partition by olt.position order by olt.position)

        from

            ordertable as ot

            inner join anothertable as at on ordertable.userid=anothertable.userid

            inner join otherlogtable as olt on anothertable.id=otherlogtable.sessionlogid
    )

select   position,  price,   orderid,

log1 ALIASNAME1, log2 ALIASNAME2

from   cte  where  rn=1

edited Mar 07 '13 at 11:50

answered Mar 06 '13 at 11:54

Ravi Singh

2,042
13
29

ok, so buylog and otherlog before the = should be without table aliases? it sure changed the errormessage - but only because the actual columns have the same name :( how/where can i rename one of them? – msa Mar 06 '13 at 11:57
Give aliases to the columns in the final select. – Ravi Singh Mar 06 '13 at 12:00
tried that, "but The multi-part identifier could not be bound." – msa Mar 06 '13 at 12:08
i'm about to cry. lets assume the column buylog and otherlog are both actually called "log". when i type ot.log aliasname, olt.log i get 2 errormessages: "The multi-part identifier "ot.log" could not be bound." and "The multi-part identifier "olt.otherlog" could not be bound." – msa Mar 06 '13 at 12:25
In final `select stmt` you cannot use table `ot` or `olt`. They exist only inside those brackets of `WITH CTE as (.............)`. – Ravi Singh Mar 06 '13 at 12:44
@ ravis i understand. but what else can i do then? when i only use column name, i get "invalid column name" error, when i use alias or full table name, i get error "The multi-part identifier could not be bound." – msa Mar 06 '13 at 13:01
ok, looks like it worked. but where do i put additional where-conditions? when i put it in the outer select, i keep getting error messages. i cant use fulltablename.column, neither tablealias.column or column in where-clause- can i put it in the cte-select? – msa Mar 06 '13 at 13:17
@msa if where clause is on any field from `position, price, orderid,log1 ALIASNAME1, log2 ALIASNAME2, rn ` put on outer select. Else in the CTE. – Ravi Singh Mar 07 '13 at 00:43

Query with value1

2 Answers2