Hi all!
In a statement I want to find the IDENTITY-column value for a row that
has the smallest value. I have tried this, but for the result i also
want to know the row_id for each. Can this be solved in a neat way,
without using temporary tables?
CREATE TABLE some_table
(
row_id INTEGER
NOT NULL
IDENTITY(1,1)
PRIMARY KEY,
row_value integer,
row_name varchar(30)
)
GO
/* DROP TABLE some_table */
insert into some_table (row_name, row_value) VALUES ('Alice', 0)
insert into some_table (row_name, row_value) VALUES ('Alice', 1)
insert into some_table (row_name, row_value) VALUES ('Alice', 2)
insert into some_table (row_name, row_value) VALUES ('Alice', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 2)
insert into some_table (row_name, row_value) VALUES ('Bob', 3)
insert into some_table (row_name, row_value) VALUES ('Bob', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 4)
insert into some_table (row_name, row_value) VALUES ('Celine', 5)
insert into some_table (row_name, row_value) VALUES ('Celine', 6)
select min(row_value), row_name from some_table group by row_name 2 1240
Jon wrote: Hi all!
In a statement I want to find the IDENTITY-column value for a row
that has the smallest value. I have tried this, but for the result i also want to know the row_id for each. Can this be solved in a neat way, without using temporary tables?
CREATE TABLE some_table ( row_id INTEGER NOT NULL IDENTITY(1,1) PRIMARY KEY,
row_value integer, row_name varchar(30) ) GO /* DROP TABLE some_table */
insert into some_table (row_name, row_value) VALUES ('Alice', 0) insert into some_table (row_name, row_value) VALUES ('Alice', 1) insert into some_table (row_name, row_value) VALUES ('Alice', 2) insert into some_table (row_name, row_value) VALUES ('Alice', 3) insert into some_table (row_name, row_value) VALUES ('Bob', 2) insert into some_table (row_name, row_value) VALUES ('Bob', 3) insert into some_table (row_name, row_value) VALUES ('Bob', 5) insert into some_table (row_name, row_value) VALUES ('Celine', 4) insert into some_table (row_name, row_value) VALUES ('Celine', 5) insert into some_table (row_name, row_value) VALUES ('Celine', 6)
select min(row_value), row_name from some_table group by row_name
*Assuming* that row_name/row_value combinations are unique, then it
would be:
select row_id,row_value,row_name from some_table t1 inner join (select
min(row_value) as row_value, row_name from some_table group by
row_name) t2 on t1.row_value = t2.row_value and t1.row_name =
t2.row_name
*Is* my assumption correct? If not, then there's some additional
grouping on the outer query, and a decision to be made on which row_id
to return (e.g. Min())
Jon (jo*********@hotmail.com) writes: In a statement I want to find the IDENTITY-column value for a row that has the smallest value. I have tried this, but for the result i also want to know the row_id for each. Can this be solved in a neat way, without using temporary tables?
Yes:
select s.row_id, x.min_value, x.row_name
from some_table s
join (select min_value = min(row_value), row_name
from some_table
group by row_name) as x on x.min_value = s.row_value
and x.row_name = s.row_name
What you see there is a *derived table*. A derived is sort of a temp
table within the query, but it is never materialized. In fact, the
optimizer may recast the computation order as long as this does not
affect the result.
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se
Books Online for SQL Server SP3 at http://www.microsoft.com/sql/techinf...2000/books.asp This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Ariel Jakobovits |
last post by:
I have a table with 2 primary keys, one is a foreign key, the other is
produced by a sequence.
I want to SELECT query for one record that has a list of the
sequence-produced values for all...
|
by: MX1 |
last post by:
I have a query written in MS Access that has a few calculated fields. Is it
possible to refer to that query in a form field. I'd like the form field to
show the sum of one of the columns from the...
|
by: Wilder |
last post by:
I'm trying to update a field in one table with the minimum values of
the field in another table. The two tables are linked via a common
field. I want to populate a date field in one table with...
|
by: Ed Havelaar |
last post by:
I have a cool function that I want to use as a default value for a column in
my table. But I can't because apparently Access doesn't allow user defined
functions in expressions for default values....
|
by: John Ortt |
last post by:
Hi there,
I am trying to do a two level embeded query but it is causing Dr Watson
errors.
The line which is causing the problem is:
CFCDeci: (Select sum (FlexChangeDeci) FROM as WHERE
(....
|
by: MLH |
last post by:
For example: Nz(,0) returns "300" if the value in
field is 300 (currency data type) and "0" if the value is
zero or null. I get strings in the query output - they are all left
aligned and I...
|
by: jefftyzzer |
last post by:
The current issue of "Oracle Magazine" has an article on creating
custom aggregate functions, which naturally got me thinking about how
to do this in DB2.
I found some articles on creating...
|
by: MP |
last post by:
vb6,ado,mdb,win2k
i pass the sql string to the .Execute method on the open connection to
Table_Name(const) db table
fwiw
(the connection opened via class wrapper:)
msConnString = "Data Source="...
|
by: Toby Gallier |
last post by:
Hello!
I have a form that is calculating averages as follows:
" =(NZ()+Nz()+Nz())/3 "
However I need to now adjust for null values , so for example if
value2 is null I would then need to...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: ryjfgjl |
last post by:
If we have dozens or hundreds of excel to import into the database, if we use the excel import function provided by database editors such as navicat, it will be extremely tedious and time-consuming...
|
by: ryjfgjl |
last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: nemocccc |
last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
|
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: 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,...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
| |