logo
Tags down

shadow

Getting all distinct values and sorting them by hour


By : Russ McDaniel
Date : October 15 2020, 08:10 AM
I wish this help you To get counts for all the hours, you'll need to left join to a list with all the hours.
And probably combined with the date of the timestamps if they span more than 1 date.
code :
-- Using a table variable for demonstration
declare @Table table (id int primary key identity(1,1), [Timestamp] datetime, CarNumber int);

-- Sample data
insert into @Table ([Timestamp], CarNumber) values
('2018-12-03 14:05:32', 433),
('2018-12-03 14:13:52', 420),
('2018-12-03 14:55:14', 433),
('2018-12-03 15:12:03', 420),
('2018-12-03 16:15:55', 570),
('2018-12-09 14:00:00', 999);

-- Query
WITH RCTE_HOURS AS
(
  select 0 as [Hour] 
  union all
  select [Hour] + 1
  from RCTE_HOURS
  where [Hour] < 23
)
SELECT h.[Hour],
 COALESCE(STUFF((
   SELECT ', ' + CONVERT(VARCHAR(10), CarNumber) 
   FROM @Table t2 
   WHERE CAST(t2.[Timestamp] AS DATE) = d.[Date]
     AND DATEPART(HOUR, t2.[Timestamp]) = h.[Hour]
     AND CarNumber IS NOT NULL
   GROUP BY CarNumber
   ORDER BY MIN(t2.[Timestamp])
   FOR XML PATH ('')
   ), 1, 2, ''),'0') AS CarNumbers
FROM 
(
  SELECT DISTINCT CAST([Timestamp] AS DATE) AS [Date]
  FROM @Table
  WHERE CAST([Timestamp] AS DATE) = CAST('2018-12-03' AS DATE)
) AS d
CROSS JOIN RCTE_HOURS h
ORDER BY d.[Date], h.[Hour];
Hour CarNumbers
---- ---------- 
0    0
1    0
2    0
3    0
4    0
5    0
6    0
7    0
8    0
9    0
10   0
11   0
12   0
13   0
14   433, 420
15   420
16   570
17   0
18   0
19   0
20   0
21   0
22   0
23   0


Share : facebook icon twitter icon

Sql query for getting top X 'distinct' values of one field when sorting by another?


By : nisha51
Date : March 29 2020, 07:55 AM
Does that help I have a table in a db that has an int field, a non-unique id field plus others. I need to sort by the int field then get the top X distinct id fields. , This should get you on the right track:
code :
SELECT 
  ID AS MyDistinctValue, 
  MIN(intfld) AS MySort
FROM
  MyTable
GROUP BY ID
ORDER BY MySort

Hour Wise Sorting Against Integer Values


By : rishabh jain
Date : March 29 2020, 07:55 AM
To fix this issue I am working on MYSQL 5.6 and I have following integer values: , Change 0 to 24:
code :
SELECT
Quantity,
SEC_TO_TIME(Quantity*60*60) as TimeFormat,
CASE WHEN SEC_TO_TIME(Quantity*60*60) BETWEEN '01:00:00' AND '12:00:00' THEN 'AM' ELSE 'PM' END AS AMPM
FROM your_table
ORDER BY AMPM ASC, TimeFormat DESC

Sorting through list and storing distinct values


By : Jonathan Ranson
Date : March 29 2020, 07:55 AM
I hope this helps . Pandas offers the unique() method to identify distinct values of a column. Then you can transpose to get into a row.
code :
>>> df2 = pd.DataFrame(df.unique()).T          # T = transpose()

          0      1      2
0  overcast  rainy  sunny
>>> df2.columns = df.unique()

   overcast  rainy  sunny
0  overcast  rainy  sunny

Count distinct values in an SQL table for each hour in a day


By : Andy
Date : March 29 2020, 07:55 AM
To fix this issue I have a table that looks like this: , You can aggregate by date and hour:
code :
select cast(timestamp as date) as thedate,
       datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by cast(timestamp as date), datepart(hour, timestamp)
order by thedate, thehour;
select datepart(hour, timestamp) as thehour,
       count(distinct CarNumber) as num_cars
from cars
group by datepart(hour, timestamp)
order by thehour;

Sql query for distinct values without sorting for a single table?


By : andyoyellow
Date : March 29 2020, 07:55 AM
wish help you to fix your issue I have only one database table and i need to get a distinct value without sorting?
Related Posts Related Posts :
  • IIF statement MS ACCESS VBA SQL
  • SQL Server group by?
  • LIKE in Elasticsearch for large texts
  • SQL - dynamic sum based on dynamic date range
  • SQL Order By: Specific Value First, then Ordering?
  • SQL Developer - SELECT statement with AVG calculation displaying multiple averages?
  • Microsoft SQL Server : return only the rows with the most recent date for each unique ID
  • How to create a queue like structure in SQL Server
  • Cross apply on columns on SQL server. Syntax error near )
  • Select count of subquery results, along with another field
  • SQL Server 2016 - Five Column to single row
  • Insert zero values for unexisting groups in Redshift
  • SQL LIKE '%...' in vba HTTP request
  • SQL Developer - How do I get MAX & AVG to draw from one column and filter from themselves?
  • How to optimize SQL query with multiple selects
  • Postgresql 11 - Create Procedure to Execute COPY function
  • Oracle VIEW - Convert VARCHAR2 to DATE and find MIN/MAX of DATE
  • In SQL, Main table should match with lookup table for the multiple columns
  • Very slow performance for inner join with restrictive where clause (very small subset of rows)
  • oracle sql 2 columns in one column
  • SQL query to get conflicting values in JSONB from a group
  • Two queries with same tables and join logic but different results
  • How to update a table joined with another table multiple times in Oracle?
  • Crystal Report how can I get data for every 5th of months
  • Finding MAX() from a serial number field which contains characters in SQL Server
  • Database Compatibility Level
  • Combine WITH statement with UNION
  • Self Nested SQL join
  • My store procedure only executes my first select statement while I wrote three?
  • Inserting Date gives error ORA-01861: literal does not match format string
  • Need to join row data into columns in Oracle
  • Syntax error Missing operator in my case statement
  • Filter the values from the source in merge query in mssql
  • How can i replace/convert number to string
  • Updating a table, on a button press, with selected information
  • Oracle SQL report generation without null
  • SQL get the names of the columns that are stored in a different table
  • Number masking & nls_parameter
  • Delete duplicate roles for SQL server
  • SQL Server: How to change collation of all columns of my select on the fly
  • SQL insert into statement leads to weird (cartesian product) result
  • check constraint sql server
  • Group specific number of rows together and generate a group id
  • shadow
    Privacy Policy - Terms - Contact Us © voile276.org