StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POQuery with value1<value2 returns multiple results
text
Body
copied!<p>My view table is like this:</p> <pre><code>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 </code></pre> <p>The expected result:</p> <pre><code>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 </code></pre> <p>This is just a part-step to get going I guess, what I actually want is:</p> <pre><code>position price 1 15 2 60 </code></pre> <p>So I need an overview of total payments per position.</p> <p>But for now, I need is: for every position and orderid, I want just the row with the highest otherlog entry.</p> <p>Now there were otherlog-times that were lower than the buylog-times, but I got them filtered out by simply <code>buylog < otherlog</code>.</p> <p>But now I don't know how to exactly show only the highest otherlog from every orderid-group. I tried with <code>max(otherlog)</code>, but it still outputs the first table.</p> <p>This is a view after joining together three tables, and I would like to get the expected result in the very same query.</p> <p>The query is like this:</p> <pre><code>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) </code></pre> <p>I'm using ms sql server 2012.</p> <p>//edit</p> <p>The query:</p> <pre><code>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 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload