How can I select records based on this specific criteria?

sql query to select a particular row from a table
excel extract list based on criteria
extract all rows from a range that meet criteria in one column
excel extract rows based on criteria
google sheets query select row
how to extract data from excel based on criteria
excel extract all rows that contain a value
how to select rows with specific text in excel

I am using SQL Server 2014 and SSMS for executing my T-SQL queries.

I have the following SQL table (extract) called Table1:

BkgID   ProfileID  ArrivalDate
 872      50        2018-01-03
 876      50        2018-01-03
 911      64        2018-02-15
 924      64        2018-04-15
 950      72        2018-05-04

I need my T-SQL query to give me the following output:

BkgID   ProfileID  ArrivalDate
 872      50        2018-01-03
 911      64        2018-02-15
 924      64        2018-04-15
 950      72        2018-05-04

The logic is that the query must list ProfileIDs which have same ArrivalDate only once. The BkgID it chooses to list in such scenario is not important.

How do I write such a query?

You can use ROW_NUMBER() window function to achieve this. The ORDER BY will determine which record gets first in the ordering of each partition.

;WITH RankingByProfile AS 
(
    SELECT
        T.BkgID,
        T.ProfileID,
        T.ArrivalDate,
        Ranking = ROW_NUMBER() OVER (PARTITION BY T.ProfileID, T.ArrivalDate ORDER BY T.BkgID ASC)
    FROM
        Table1 AS T
)
SELECT
    R.BkgID,
    R.ProfileID,
    R.ArrivalDate
FROM
    RankingByProfile AS R
WHERE
    R.Ranking = 1

You can also use GROUP BY and retrieve the MIN(BkgID), but you won't be able to access other columns without aggregate functions.

SELECT
    MinBkgID = MIN(T.BkgID),
    T.ProfileID,
    T.ArrivalDate
FROM
    Table1 AS T
GROUP BY
    T.ProfileID,
    T.ArrivalDate

Use Queries to show records based on conditions – Documentation , reorder the columns; rename or relabel column headers; show rows (records) based on specified criteria; sort data. warning  If you want to solve this problem with fewer steps, you can use Kutools for Excel’s Select Specific Cells function to select the rows that meet the criteria, and then copy them to another location. 1. Select the column you extract rows based on, and click Kutools > Select > Select Specific Cells. See screenshot: 2.

select BkgID, ProfileID, ArrivalDate
from (
  select BkgID, ProfileID, ArrivalDate,
    ROW_NUMBER() OVER(PARTITION BY ProfileID, ArrivalDate Order By BkgID) RowIdx
  from yourTable)
where RowIdx = 1

Extract all rows from a range that meet criteria in one column, If you want to extract records based on criteria and not a numerical range then read You can follow along if you select cell A19, go to tab "Formulas" on the ribbon Step 1 - Filter a specific column in cell range $A$2:$D$11. In this Excel tutorial from ExcelIsFun, the 186th installment in their series of Excel magic tricks, you'll see how to extract records that meet two conditions (criteria) and list them in order with no extra rows between records as well as how to create a formula to extract part of a table from a larger table based on two criteria.

You can also use correlation approach :

select t.*
from table t
where BkgID = (select top 1 t1.BkgID
               from table t1
               where t1.ProfileID = t.ProfileID and t1.ArrivalDate = t.ArrivalDate
               order by t1.BkgID asc
              );

This assumes ArrivalDate has some reasonable date format else use cast() function.

How to select records that meets specific criteria, To solve this problem, I did the following: Created table and data: CREATE TABLE table1 ( id INTEGER NOT NULL, stage VARCHAR (10), nextstage VARCHAR  In the Select Specific Cells dialog box, check Cell from Selection type, and set your criteria under Specific type as you need, see screenshot: 3. After setting the criteria, click OK or Apply button, a prompt message will remind you the number of the selected cells. And all of cells which accord with the criteria have been selected from the range.

sql server, To solve this problem, I did the following: Created table and data: CREATE TABLE table1 ( id INTEGER NOT NULL, stage VARCHAR (10),  In the Choose Builder dialog box, click Combo Box Wizard, and then click OK. In the Combo Box Wizard dialog box, select the Find a record on my form based on the value I selected in my combo box option, and then click Next. Include the ProductID and ProductName fields, and then click Next. Click Finish.

Find and select cells that meet specific conditions, Also, use Go To to find only the cells that meet specific criteria,—such as the last For more information, see Select cells, ranges, rows, or columns on a worksheet. and the cell that is used in the comparison for each additional row is located  Lets filter records based on conditions applied to column D. Click black arrow next to header in Column D, see image below. Click "Number Filters". Click "Between". Type 4 and 6 in the dialog box shown below. Click OK button. Back to top. Extract all rows from a range that meet criteria in one column [Advanced Filter]

Examples of query criteria - Access - Office Support, Use criteria in an Access query to find specific information from your desktop database. depending on the data type of the field to which they apply and your specific see the article Apply a filter to view select records in an Access database. For example, = "Chicago" is an expression that Access can compare to values in a text field in a query. If the value for that field in a given record is "Chicago", Access includes the record in the query results. Here are some examples of commonly used criteria you can use as a starting point to create your criteria.

Comments
  • You can easily achieve this using ROW_NUMBER. If you google this keyword you will find many examples of how to use this function.