First Occurence of a Field Pair in SQL

sql select first occurrence in each group
sql select first occurrence by date
sql find first occurrence of character in string
sql select first record matching criteria
sql group by first occurrence
first sql
sql count first occurrence
first value in sql

My table looks like this:

+------------+---------------+--------+
|    City    | Category Name | Orders |
+------------+---------------+--------+
| London     | Components    |     29 |
| Woolston   | Bikes         |     16 |
| Union City | Clothing      |     13 |
| London     | Bikes         |     13 |
| Union City | Bikes         |     11 |
| Union City | Components    |     11 |
| Woolston   | Clothing      |     11 |
| Woolston   | Components    |      8 |
| Woolston   | Accessories   |      8 |
| Union City | Accessories   |      8 |
| London     | Clothing      |      4 |
| London     | Accessories   |      1 |
+------------+---------------+--------+

The goal is to get each city and its most popular category, so:

+------------+---------------+
|    City    | Category Name |
+------------+---------------+
| London     | Components    |
| Woolston   | Bikes         |
| Union City | Clothing      |
+------------+---------------+

In this case, I'd need to pick the first occurrence of each city in the first table along with its category.

I've tried using the standard:

SELECT City, Max(Orders) 
FROM Table
GROUP BY City

That however starts to become problematic as soon as you try to add the category name into the mix and I also do not want the Orders field in the new table.

Is there a way to do this nicely?

You can use window functions or a correlated subquery:

select t.*
from t
where t.orders = (select max(t2.orders) from t t2 where t2.city = t.city);

As a window function:

select t.*
from (select t.*, row_number() over (partition by city order by orders desc) as seqnum
      from t
     ) t
where seqnum = 1;

Get the first occurrence of a repeated record number, I have a table that has an Id column and a RecordNumber column They are distinct based on the other columns, I am using Select Distinct. sql What I need to do is find the FIRST occurrence of that record number and  The FIRST () function returns the first value of the selected column. SQL FIRST () Syntax SELECT FIRST (column_name) FROM table_name; Note: The FIRST () function is only supported in MS Access.

    select a.City, a.[Category Name]
    from   your_table a
    where  a.Orders = ( select max(Orders) 
                        from   your_table 
                        where  a.City = City );

Determine consecutive occurrences of values, Unless you have an additional column with the order of rows - there is no Because SQL is based on the theory of relations and in this concept the and then pair first items with last items using ROW_NUMBER() as keys. FIRST_VALUE is nondeterministic. For more information, see Deterministic and Nondeterministic Functions. Examples A. Using FIRST_VALUE over a query result set. The following example uses FIRST_VALUE to return the name of the product that is the least expensive in a given product category.

You can try below -

SELECT City,category_name, order
FROM Table a where order in (select max(order) from table b where a.city=b.city)

Finding Duplicates with SQL, Finding Duplicates with SQL was first published on October 06, 2004. i want to include a 4th field that would show the number of occurence based on Name,  SQL Server FIRST_VALUE() function overview. The FIRST_VALUE() function is a window function that returns the first value in an ordered partition of a result set. The following shows the syntax of the FIRST_VALUE() function: FIRST_VALUE ( scalar_expression ) OVER ( [PARTITION BY partition_expression,

This could be worth a try:

Select Distinct city, MaxCnt.category_name
from t
Cross APPLY(Select Top 1 t2.city, t2.category_name, Count(*) as RecCnt  from t t2 where t2.city = t.city Group BY t2.city, t2.category_name Order By RecCnt DESC) as MaxCnt

CSS :nth-of-type() Selector, Well organized and easy to understand Web building tutorials with lots of examples of how to use HTML, CSS, JavaScript, SQL, PHP, Python, Bootstrap, Java  First occurrence —————-1. Find Last occurrence of any character/ word in the string : In the example given below, we need to search for the last occurrence of word ‘the’ in the sentence. DECLARE @String AS VARCHAR(100) DECLARE @Search_String AS VARCHAR(100) SET @String ='The SQL SERVER is one of the best applications of Microsoft

In SQL, how do I count DISTINCT over multiple columns?, Remember that you must include the columns that are before the count in GROUP BY: SELECT SQL Server will first execute the query in the from clause. I agree with Niels van der Gailen, but I wouldd a sort to make sure I got what I intended SELECT * FROM `tableName` ORDER BY SOMEID ASC LIMIT 1 Some working code

Table Value Constructor (Transact-SQL), A value for each column in the table must be specified or the column list must explicitly specify the columns for each incoming value. DEFAULT These records are distinct and have a unique id.. The only thing that repeats in this data set is the RecordNumber, which I want to happen. What I need to do is find the FIRST occurrence of that record number and populate the comment field with some data, and set all the other comment fields with the same RecordNumber to be NULL, which is why I was using a CASE statement to start, so that IF

Documentation: 10: 8.16. Composite Types, Here the first field is a non-NULL empty string, the third is NULL. item is taken to be a table name, not a column name of on_hand , per SQL syntax rules. For instance, to select just one field from the result of a function that returns a composite (Also, a pair of double quotes within a double-quoted field value is taken to  What I want is the first occurrence of every part number, which is easy if I use: SELECT Part_Number, MIN(Order_Date) AS date FROM dbo.Job GROUP BY Part_Number Select all Open in new window

Comments
  • "Is there a way to do this nicely?" This dataset seams not to have equal values within a "group", but is it possible to have those?
  • Thanks, correlated subquery was just what I needed.
  • Also worked perfectly - Gordon was faster though haha. Thank you anyway!
  • That's fine only if every record on top of the table has a unique city. Had the fourth record's orders been 14 we'd have 2 Londons in the resulting table.