I've got the following table data:
11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00
And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.
Thanks! 5 3626
dale....@gmail.com wrote: I've got the following table data:
11652 5.99 11652 0.14 12996 5.03 12996 0.12 12996 7.00
And I need to write a query to return only rows 2 and 4, since the remaining rows have duplicate IDs. I've tried the Group By, but am having no luck.
Thanks!
What do you mean by "rows 2 and 4"? Those numbers refer to positions in
the list of values you posted, but SQL Server knows nothing about that
because tables in SQL have no logical order at all. In other words you
haven't given enough information to answer your question.
If these are the only two columns you have then probably the best you
can do is:
SELECT col1, MIN(col2) AS col2
FROM your_table
GROUP BY col1 ;
or:
SELECT col1, MAX(col2) AS col2
FROM your_table
GROUP BY col1 ;
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
Why 2 and 4? Why not 1 & 3 or 1 & 5? What are you using as your
discriminator?
--
Tom
----------------------------------------------------
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Toronto, ON Canada
..
<da******@gmail.com> wrote in message
news:11**********************@t31g2000cwb.googlegr oups.com...
I've got the following table data:
11652 5.99
11652 0.14
12996 5.03
12996 0.12
12996 7.00
And I need to write a query to return only rows 2 and 4, since the
remaining rows have duplicate IDs. I've tried the Group By, but am
having no luck.
Thanks!
Thanks for the quick response!
Here's my complete query:
SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue,
DATEADD(S, Data.[Time], '1jan1970') AS EventDate,
Job.KSName, GETDATE() AS CURDATE
FROM DataHeader INNER JOIN
Data ON DataHeader.DataID = Data.DataID INNER JOIN
Object INNER JOIN
Job ON Object.ObjID = Job.MachineObjID ON
DataHeader.JobID = Job.JobID
Group By Job.JobID
But I'm getting the following error:
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Object.Name' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Data.Time' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
Server: Msg 8120, Level 16, State 1, Line 1
Column 'Job.KSName' is invalid in the select list because it is not
contained in either an aggregate function or the GROUP BY clause.
David Portas wrote: dale....@gmail.com wrote: I've got the following table data:
11652 5.99 11652 0.14 12996 5.03 12996 0.12 12996 7.00
And I need to write a query to return only rows 2 and 4, since the remaining rows have duplicate IDs. I've tried the Group By, but am having no luck.
Thanks!
What do you mean by "rows 2 and 4"? Those numbers refer to positions in the list of values you posted, but SQL Server knows nothing about that because tables in SQL have no logical order at all. In other words you haven't given enough information to answer your question.
If these are the only two columns you have then probably the best you can do is:
SELECT col1, MIN(col2) AS col2 FROM your_table GROUP BY col1 ;
or:
SELECT col1, MAX(col2) AS col2 FROM your_table GROUP BY col1 ;
-- David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem. Including CREATE TABLE and INSERT statements usually helps. State what version of SQL Server you are using and specify the content of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx --
dale....@gmail.com wrote: Thanks for the quick response!
Here's my complete query:
SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue, DATEADD(S, Data.[Time], '1jan1970') AS EventDate, Job.KSName, GETDATE() AS CURDATE
FROM DataHeader INNER JOIN Data ON DataHeader.DataID = Data.DataID INNER JOIN Object INNER JOIN Job ON Object.ObjID = Job.MachineObjID ON DataHeader.JobID = Job.JobID
Group By Job.JobID
But I'm getting the following error:
Server: Msg 8120, Level 16, State 1, Line 1 Column 'Object.Name' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Server: Msg 8120, Level 16, State 1, Line 1 Column 'Data.Time' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause. Server: Msg 8120, Level 16, State 1, Line 1 Column 'Job.KSName' is invalid in the select list because it is not contained in either an aggregate function or the GROUP BY clause.
Any column that you don't want to group by needs to be enclosed in an
aggregate function (MIN or MAX for example). Your problem is obviously
a bit different to what you first asked for. The best way to post a
problem like this is to include enough code so that others can
reproduce it. See: http://www.aspfaq.com/etiquette.asp?id=5006
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online: http://msdn2.microsoft.com/library/m...S,SQL.90).aspx
--
(da******@gmail.com) writes: Thanks for the quick response!
Here's my complete query:
SELECT Object.Name, Job.JobID, MAX(Data.[Value]) as NewValue, DATEADD(S, Data.[Time], '1jan1970') AS EventDate, Job.KSName, GETDATE() AS CURDATE
FROM DataHeader INNER JOIN Data ON DataHeader.DataID = Data.DataID INNER JOIN Object INNER JOIN Job ON Object.ObjID = Job.MachineObjID ON DataHeader.JobID = Job.JobID
Group By Job.JobID
This is possible correct version of your query, but most probably not.
It's just a piece of guesswork.
SELECT o.Name, j.JobID, mx.NewValue,
DATEADD(ss, d.[Time], '1 jan 1970') AS EventDate,
j.KSName, GETDATE() AS CURDATE
FROM (SELECT j.JobID, NewValue = MAX(d.[Value])
FROM DataHeader dh
JOIN Job j ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID) AS mx
JOIN Job j ON mx.JobID = j.JobID
JOIN DataHeader dh ON dh.JobID = j.JobID
JOIN Data d ON dh.DataID = d.DataID
JOIN Object o ON o.ObjID = j.MachineObjID
GROUP BY j.JobID
For this type of questions it helps if you include descriptions of
your tables, including keys. Preferably in form of CREATE TABLE
statements. Sample data is also a good idea, even better if as
INSERT statements, as that makes it easy to post a tested solution.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server 2005 at http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at http://www.microsoft.com/sql/prodinf...ons/books.mspx This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Patrick G. |
last post by:
Greetings all:
ASP VB, SQL Svr 2000
I am pulling data from 3 tables.
table1 holds item details
table2 holds publication types and the item id from table1
table3 holds category types and...
|
by: Damien Cobbs |
last post by:
Is there a way to remove duplicate rows from a table that does not
have a primary key leaving one copy of the row without comparing each
column to each other as suggested by the code example below:...
|
by: Diego Rey |
last post by:
Hi everyone.
How can I get the unique row from a table which contains multiple rows
that have exactly the same values.
example:
create table test (
c1 as smallint,
c2 as smallint,
c3 as...
|
by: TaeHo Yoo |
last post by:
I have a table that has more than 1 milion rows so practically it is
impossible to remove all duplicate rows by hand.
Could you help me to remove those duplicate rows at all?
This table doesn't...
|
by: Jon Maz |
last post by:
Hi,
I have a MySql problem I hope someone can help me with. I'm trying to run
an update on a linking table, the update is running into a Primary Key
constraint violation, and in my workaround...
|
by: kiki |
last post by:
I have two datasets with the same schema,each of which has some
datarows.After i merge them into one datset i found that there were
some duplicate rows in these rows.then how can i remove the...
|
by: B.N.Prabhu |
last post by:
Hi,
I have a DataTable with several rows. Its having 20 Columns. when i click
the Insert button then i have to check the Database Rows. Whether these new
rows are already available in the...
|
by: Rich |
last post by:
Hello,
I am just checking if there is a property or technique for displaying or
retrieving from a dataTable the top 1 row(s) for rows containing duplicate
keys (IDs). I have to pull data from a...
|
by: Hariny |
last post by:
Hi,
Here is my query:
select a.out_tar||'~'|| a.out_csi_id||'~'||a.out_cust_name||'~'||a.out_id||'~'||b.out_id||'~'||a.out_category||'~'||
b.out_category||'~'||
to_char(a.out_start_date, 'fm...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: aa123db |
last post by:
Variable and constants
Use var or let for variables and const fror constants.
Var foo ='bar';
Let foo ='bar';const baz ='bar';
Functions
function $name$ ($parameters$) {
}
...
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |