Select Top Rows Until Value in Specific Column Has Appeared Twice

Select top rows until value in specific column has appeared twice

Steps:

  1. Create a row number, rn, over all rows in case id is not in sequence.
  2. Create a row number, approv_rn, partitioned by EmailApproved so we know when EmailApproved = 1 for the second time
  3. Use a outer apply to find the row number of the second instance of EmailApproved = 1
  4. In the where clause filter out all rows where the row number is >= the value found in step 3.
  5. If there is 1 or 0 EmailApproved records available then the outer apply will return null, in which case return all available rows.
with test as
(
select *,
rn = row_number() over (order by Created desc),
approv_rn = row_number() over (partition by EmailApproved
order by Created desc)
from @Test
)
select *
from test t
outer apply
(
select x.rn
from test x
where x.EmailApproved = 1
and x.approv_rn = 2
) x
where t.rn < x.rn or x.rn is null
order by t.Created desc;

How to select only the first rows for each unique value of a column?

A very simple answer if you say you don't care which address is used.

SELECT
CName, MIN(AddressLine)
FROM
MyTable
GROUP BY
CName

If you want the first according to, say, an "inserted" column then it's a different query

SELECT
M.CName, M.AddressLine,
FROM
(
SELECT
CName, MIN(Inserted) AS First
FROM
MyTable
GROUP BY
CName
) foo
JOIN
MyTable M ON foo.CName = M.CName AND foo.First = M.Inserted

Repeat Rows N Times According to Column Value

You could do that with a recursive CTE using UNION ALL:

;WITH cte AS
(
SELECT * FROM Table1

UNION ALL

SELECT cte.[ID], cte.ProductFK, (cte.[Order] - 1) [Order], cte.Price
FROM cte INNER JOIN Table1 t
ON cte.[ID] = t.[ID]
WHERE cte.[Order] > 1
)
SELECT [ID], ProductFK, 1 [Order], Price
FROM cte
ORDER BY 1

Here's a working SQLFiddle.

Here's a longer explanation of this technique.


Since your input is too large for this recursion, you could use an auxillary table to have "many" dummy rows and then use SELECT TOP([Order]) for each input row (CROSS APPLY):

;WITH E00(N) AS (SELECT 1 UNION ALL SELECT 1),
E02(N) AS (SELECT 1 FROM E00 a, E00 b),
E04(N) AS (SELECT 1 FROM E02 a, E02 b),
E08(N) AS (SELECT 1 FROM E04 a, E04 b),
E16(N) AS (SELECT 1 FROM E08 a, E08 b)
SELECT t.[ID], t.ProductFK, 1 [Order], t.Price
FROM Table1 t CROSS APPLY (
SELECT TOP(t.[Order]) N
FROM E16) ca
ORDER BY 1

(The auxillary table is borrowed from here, it allows up to 65536 rows per input row and can be extended if required)

Here's a working SQLFiddle.

See whether an item appears more than once in a database column

It should be:

SELECT SalesID, COUNT(*)
FROM AXDelNotesNoTracking
GROUP BY SalesID
HAVING COUNT(*) > 1

Regarding your initial query:

  1. You cannot do a SELECT * since this operation requires a GROUP BY
    and columns need to either be in the GROUP BY or in an aggregate
    function (i.e. COUNT, SUM, MIN, MAX, AVG, etc.)
  2. As this is a GROUP BY operation, a HAVING clause will filter it
    instead of a WHERE

Edit:

And I just thought of this, if you want to see WHICH items are in there more than once (but this depends on which database you are using):

;WITH cte AS (
SELECT *, ROW_NUMBER() OVER (PARTITION BY SalesID ORDER BY SalesID) AS [Num]
FROM AXDelNotesNoTracking
)
SELECT *
FROM cte
WHERE cte.Num > 1

Of course, this just shows the rows that have appeared with the same SalesID but does not show the initial SalesID value that has appeared more than once. Meaning, if a SalesID shows up 3 times, this query will show instances 2 and 3 but not the first instance. Still, it might help depending on why you are looking for multiple SalesID values.

Edit2:

The following query was posted by APC below and is better than the CTE I mention above in that it shows all rows in which a SalesID has appeared more than once. I am including it here for completeness. I merely added an ORDER BY to keep the SalesID values grouped together. The ORDER BY might also help in the CTE above.

SELECT *
FROM AXDelNotesNoTracking
WHERE SalesID IN
( SELECT SalesID
FROM AXDelNotesNoTracking
GROUP BY SalesID
HAVING COUNT(*) > 1
)
ORDER BY SalesID

Select rows until a total amount is met in a column (mysql)

How about this? Using two variables.

SQLFIDDLE DEMO

Query:

set @tot:=0;
set @sup:=0;

select x.id, x.supplier, x.ctot
from (
select id, supplier, qty,
@tot:= (case when @sup = supplier then
@tot + qty else qty end) as ctot,
@sup:=supplier
from demo
order by supplier asc, id desc) x
where x.ctot >=5
;

| ID | SUPPLIER | CTOT |
------------------------
| 2 | 1 | 5 |
| 1 | 1 | 7 |
| 3 | 2 | 5 |

select rows in sql with latest date for each ID repeated multiple times

This question has been asked before. Please see this question.

Using the accepted answer and adapting it to your problem you get:

SELECT tt.*
FROM myTable tt
INNER JOIN
(SELECT ID, MAX(Date) AS MaxDateTime
FROM myTable
GROUP BY ID) groupedtt
ON tt.ID = groupedtt.ID
AND tt.Date = groupedtt.MaxDateTime

Select rows with same id but different value in another column

This ought to do it:

SELECT *
FROM YourTable
WHERE ARIDNR IN (
SELECT ARIDNR
FROM YourTable
GROUP BY ARIDNR
HAVING COUNT(*) > 1
)

The idea is to use the inner query to identify the records which have a ARIDNR value that occurs 1+ times in the data, then get all columns from the same table based on that set of values.

SQL Query To Obtain Value that Occurs more than once

For SQL Server 2005+

;WITH T AS
(
SELECT *,
COUNT(*) OVER (PARTITION BY Lastname) as Cnt
FROM Students
)
SELECT * /*TODO: Add column list. Don't use "*" */
FROM T
WHERE Cnt >= 3

How can I SELECT rows with MAX(Column value), PARTITION by another column in MYSQL?

You are so close! All you need to do is select BOTH the home and its max date time, then join back to the topten table on BOTH fields:

SELECT tt.*
FROM topten tt
INNER JOIN
(SELECT home, MAX(datetime) AS MaxDateTime
FROM topten
GROUP BY home) groupedtt
ON tt.home = groupedtt.home
AND tt.datetime = groupedtt.MaxDateTime


Related Topics



Leave a reply



Submit