How can I combine
select distinct ids from studenttable
……………then for each id…………………
select top 1 * from studenttable where id = @id order by timestamp desc
……into one T-SQL statement (IE without using loops/queues)?
BTW I meant cursors not queues
Give this a try:
SELECT a.* FROM studenttable a,
(SELECT id, MAX(timestamp) FROM studenttable GROUP BY id) b
WHERE [...]
I have a table with 3 fields: zip, city, state. One row for every zip code in the US, it contains about 39,000 rows.
I want to search by city name alone. Some cities, such as "Rockford", will return many rows, in several different states.
If I do "SELECT * FROM ziplist WHERE city = ‘Rockford’", I [...]
Hi, I have the query
select distinct programcode, dept from studentaffairsview where termcode = ‘200900′;
I want to only show results where the programcode appears more than once. Can this be done?
Thanks
This’ll do it.
Select Count(programcode) as Instances, programcode from studentaffairsview
Where termcode = ‘200900′
Group by programcode
Having Count(programcode) > 1
I have a query that goes something like this:
select * from table1
where table1.id in(select distinct id from stuff)
union
select * from table2
where table2.id in(select distinct id from other_stuff);
This query ran like crap. To change it, all I did was add a "group by id" to the end of the [...]
select bednr from bed
where bednr not in (select distinct bednr from patient);
This does not seem to work. Its basicly to see if a bed is in use or not.
select bednr from bed
where bednr not in (select distinct bednr from patient
where bednr is not null);
select mname,id,name from item_mansup where sid= 488 and id in (select distinct itemid from item_tr where itemid not in(select distinct id from item_mansup where sid = 971)
this qry is not running in access any body help me ?
If this is the *exact* query, you’re missing a second closing ) after the second embedded select. [...]
. Assume an automobile license plate number consists of two letters followed by a three digit number. How many distinct license plate numbers can be formed if
(a) there are no restrictions and
(b) the letters O and I are not used?
(c) What is the probability of selecting at random a license plate that ends in an [...]
Hello,
I am very much a beginner with SQL, and I was hoping for some help with a script.
I need to pull records from a table with distinct values for one of the fields and a given value for another field. I want the results to include all of the fields.
In other words…I need a [...]
1.A pair of dice is tossed. Find the probability of getting a) a total of 8? B) at most a total of 5?
2.Two cards are drawn in succession from a deck without replacement. What is the probability that both cards are greater than 2 and less than 8?
3.If each coded item in catalog begins with [...]
Say, for example, I have a table with 28 items. Each item has a cost and a name. I want each distinct item and a total of the tables cost.
I tried a function but I am getting some errors.
Here is the code:
CREATE FUNCTION dbo.GetPricePerPiece
(
@orderID nvarchar(50)
)
RETURNS float
AS
BEGIN
DECLARE @resultSet table
DECLARE @pricePerPiece float
SELECT @resultSet=SELECT DISTINCT [Name], Cost FROM [...]
