May 4, 2011 at 12:43 pm
Hi,
I've a table with those columns (datetime,int,int) in this table i insert every hour a record of getdate() and two int value.
my question is how i can write the query that will give me one row from every day that have the biggest value in the second and third column?
THX
May 4, 2011 at 12:46 pm
You need to post some DDL and sample data. That would allow us to provide a better answer.
Dan
If only I could snap my figures and have all the correct indexes apear and the buffer clean and.... Start day dream here.
May 4, 2011 at 12:52 pm
Are there rules about the values in the int columns?
For example:
CREATE TABLE #T
(ID INT IDENTITY
PRIMARY KEY,
DT DATETIME,
Int1 INT,
Int2 INT) ;
INSERT INTO #T
(DT, Int1, Int2)
VALUES (DATEADD(HOUR, 0, GETDATE()), 50, 1),
(DATEADD(HOUR, 1, GETDATE()), 30, 30),
(DATEADD(HOUR, 2, GETDATE()), 60, -50),
(DATEADD(HOUR, 3, GETDATE()), 40, 20) ;
SELECT *,
ROW_NUMBER() OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, DT), 0) ORDER BY Int1
+ Int2) AS Seq1,
ROW_NUMBER() OVER (PARTITION BY DATEADD(DAY, DATEDIFF(DAY, 0, DT), 0) ORDER BY Int1, Int2) AS Seq2
FROM #T
ORDER BY ID ;
The "sum it up" version gives very different results from the "order by" version. Other math could produce different results.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 4, 2011 at 12:59 pm
Mad-Dog (5/4/2011)
Hi,I've a table with those columns (datetime,int,int) in this table i insert every hour a record of getdate() and two int value.
my question is how i can write the query that will give me one row from every day that have the biggest value in the second and third column?
THX
what does "The biggest value in the second and third " column mean? is it BOTH columns, or the the biggest value in EITHER column? you've got a lot of posts, I'd figure you know what's needed to ask a question that will generate testable answers by now.
assuming BOTH columns, and column2 is more important than column3, you might be able to use row_number and a subquery; something like this?
SELECT
TheDate,
SECONDCOLUMN,
THIRDCOLUMN
FROM
(SELECT
DATEADD(dd, DATEDIFF(dd,0,YOURDATECOLUMN), 0) As TheDate,
SECONDCOLUMN,
THIRDCOLUMN,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,YOURDATECOLUMN), 0), SECONDCOLUMN, THIRDCOLUMN
ORDER BY DATEADD(dd, DATEDIFF(dd,0,YOURDATECOLUMN), 0), SECONDCOLUMN DESC, THIRDCOLUMN DESC ) AS RW
FROM YOURTABLE
) MyAlias
WHERE RW = 1
Lowell
May 4, 2011 at 1:02 pm
Using GSquared's sample data, is this what you are trying to acheive?
SELECT DATEADD( DD , DATEDIFF(DD,0,DT) ,0) Days ,
MAX(Int1) Int1_Mx,
MAX(Int2) Int2_Mx
from #T
group by DATEADD( DD , DATEDIFF(DD,0,DT) ,0)
May 5, 2011 at 2:33 am
here is the table
CREATE TABLE [dbo].[TBL_REPORT_COLLECT_USERS_CONNECTION](
[RUN_TIME] [datetime] NOT NULL,
[USERS_COUNT] [int] NOT NULL,
[CONNECTION_COUNT] [int] NOT NULL
here is the insert every hour
INSERT INTO TBL_REPORT_COLLECT_USERS_CONNECTION(RUN_TIME,USERS_COUNT,CONNECTION_COUNT)
SELECT GETDATE()AS 'RUN_TIME',COUNT(DISTINCT loginame) AS 'USERS_COUNT',count(spid)AS 'CONNECTIONS_COUNT'
FROM master.sys.sysprocesses
WHERE spid > 50
now i want to run a select that will give me the high results from every day.
THX
May 5, 2011 at 2:49 am
Mad-Dog (5/5/2011)
here is the tableCREATE TABLE [dbo].[TBL_REPORT_COLLECT_USERS_CONNECTION](
[RUN_TIME] [datetime] NOT NULL,
[USERS_COUNT] [int] NOT NULL,
[CONNECTION_COUNT] [int] NOT NULL
here is the insert every hour
INSERT INTO TBL_REPORT_COLLECT_USERS_CONNECTION(RUN_TIME,USERS_COUNT,CONNECTION_COUNT)
SELECT GETDATE()AS 'RUN_TIME',COUNT(DISTINCT loginame) AS 'USERS_COUNT',count(spid)AS 'CONNECTIONS_COUNT'
FROM master.sys.sysprocesses
WHERE spid > 50
now i want to run a select that will give me the high results from every day.
THX
Getting the high results from every day depends upon how you define "high results", since there are two columns to consider. Lowell and G2 have already asked for a definition. Can you elaborate?
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
May 5, 2011 at 3:14 am
i need the high result from USERS_COUNT every day.
May 5, 2011 at 6:42 am
so does this give you the results you were looking for?
SELECT
TheDate,
USERS_COUNT,
CONNECTION_COUNT
FROM
(SELECT
DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0) As TheDate,
USERS_COUNT,
CONNECTION_COUNT,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT, CONNECTION
ORDER BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT DESC, CONNECTION_COUNT DESC ) AS RW
FROM [TBL_REPORT_COLLECT_USERS_CONNECTION]
) MyAlias
WHERE RW = 1
Lowell
May 5, 2011 at 7:18 am
Lowell (5/5/2011)
so does this give you the results you were looking for?
SELECT
TheDate,
USERS_COUNT,
CONNECTION_COUNT
FROM
(SELECT
DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0) As TheDate,
USERS_COUNT,
CONNECTION_COUNT,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT, CONNECTION
ORDER BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT DESC, CONNECTION_COUNT DESC ) AS RW
FROM [TBL_REPORT_COLLECT_USERS_CONNECTION]
) MyAlias
WHERE RW = 1
this select statement give me a lot of results from the same day while i need the highest USERS_COUNT column from every day.
May 5, 2011 at 7:23 am
doh; it should be more like GSquared; remove the USERS_COUNT, CONNECTION from the PARTITION statement.
SELECT
TheDate,
USERS_COUNT,
CONNECTION_COUNT
FROM
(SELECT
DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0) As TheDate,
USERS_COUNT,
CONNECTION_COUNT,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0)
ORDER BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT DESC, CONNECTION_COUNT DESC ) AS RW
FROM [TBL_REPORT_COLLECT_USERS_CONNECTION]
) MyAlias
WHERE RW = 1
Lowell
May 5, 2011 at 7:57 am
it looks good,but how i can view the "real" datetime as it was insert into the table and not 00:00:00 in the time?
THX
May 5, 2011 at 8:07 am
just add additional columns from the original table to teh query:
SELECT
TheDate,
RUN_TIME,
USERS_COUNT,
CONNECTION_COUNT
FROM
(SELECT
DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0) As TheDate,,
RUN_TIME,
USERS_COUNT,
CONNECTION_COUNT,
ROW_NUMBER() OVER (PARTITION BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0)
ORDER BY DATEADD(dd, DATEDIFF(dd,0,RUN_TIME), 0), USERS_COUNT DESC, CONNECTION_COUNT DESC ) AS RW
FROM [TBL_REPORT_COLLECT_USERS_CONNECTION]
) MyAlias
WHERE RW = 1
Lowell
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply