I have a query like this: The result looks like this: Now I want to get the first row for each category_id. So it must be the biggest num and its business_id. So the expected result would be: How can I do that? Answer if your MySQL version support ROW_NUMBER + window function, you can try to use ROW_NUMBER to
Tag: sql
SQL Counting Rows based on break values
Frequently I need to do something in SQL Server that I just don’t know how to do properly. Basically, if I have a table #sample that is like this: DEPARTMENT DATE ACCOUNTING A FINANCE A PAYROLL A ACCOUNTING B PAYROLL A FINANCE A PAYROLL B PAYROLL C I need to query that table to get an output that is thi…
Join Hours and Minute column for showing in Decimal format SQL Query
I have two datetime columns in a SQL Server table: From which I have separated hours and minutes through SQL query The output is Now, for further grouping the data, I need to join Hours and Minutes columns in one column for getting output like this Answer You can try converting both hours and mins to varchar …
Get rows whose JSON array contains an object with a value
I have a query that returns one JSON column named record, which is generated by a subquery. SELECT record FROM (my json query) records; Each record contains an array of tag objects: I want to modify my query to select all records where tags contains a tag with a specific ID but I’m not sure how to go ab…
SQL Find/Count similar duplicates
I am trying to to do a count on all records that are similar. Eg. I have 2 records 1 is 1A Smith St and the other is 1 Smith St. So this data needs to be returned/counted as 1. Any assistance would be gratefully appreciated. Thanks Answer Counting those where the (street, number) combination isn’t uniqu…
Select Rows That Does Not Contain any Negative Or Missing Value
Assume a database table has a few hundred columns. In SQL statements, how would you select rows/records that do not contain any negative or missing value? Can you do it using the sqldf package for R users? Here is an example of data frame with 6 rows and 2 columns: The SQL statement(s) should only return a ta…
Filter a Dataframe using a subset of it and two specific fields in spark/scala [closed]
Closed. This question needs debugging details. It is not currently accepting answers. Edit the question to include desired behavior, a specific problem or error, and the shortest code necessary to reproduce the problem. This will help others answer the question. Closed 10 months ago. Improve this question I h…
Is there a more efficient way to append multiple columns from the same table?
I’d like to return multiple values from the same record, with different columns of the same type appended under one another. I could do multiple joins, but that seems very inefficient because of the multiple table scans. Returns: VisID Vis Home HomeID arenn001 Nolan Arenado Colin Moran morac001 badeh001…
SQL Query: Search Across Multiple Fields in JOINed Tables
SQL Version: MySQL 8.0 or SQL Server SQL Fiddle: https://www.db-fiddle.com/f/wcHeXkcynUiYP3qzryoYJ7/6 I have a table of images and a table of tags that link to those images. I want to be able to pass (2) tags to the query and have it select only the images that match BOTH tags. For example, I want to pass Nov…
SQL query does not work in Excel but works in Access
For the code listed below, it runs fine except for the first SQL query. I’m pulling address and state information from the workbook, and running a query on the information to find the count of how many times the address appears in the table. If I run the code and stop it before the query is sent to Acce…