|
Hi!
I've to select the most recently added field in a column. i.e the last value in a column. How to select it?
|
|
|
|
|
Do you mean to get the last row of data added to the table? Your question is not clear.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
I'll describe it.
I'm inserting TeamIds to the table from one class(TeamIds are selected ids of a list in a Menu page). I've to display a color of the selected team(TeamID)over the remaining pages. On Clicking a button, I've stored the selected index of the list control(TeamID) to the Team table. From other pages(other classes),
I've to query the recently added TeamID(last one in the table). How to do this?
|
|
|
|
|
Quick thought: why not add the id to the session and use that? Save a trip back to the db to fetch what you already have.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
You could use a trigger to include a timestamp on each row that is inserted or updated. Then your select would be on the most recently timestamped row. However this may not work if your inserts and updates can occur faster than the smallest time span for your timestamp. 
Chris Meech
I am Canadian. [heard in a local bar]
In theory there is no difference between theory and practice. In practice there is. [Yogi Berra]
|
|
|
|
|
If you're using an autonumber field as a primary key, select on the MAX value of that field. Alternatively, get the number of rows in the main table and fetch the (N-1)th row.
"A Journey of a Thousand Rest Stops Begins with a Single Movement"
|
|
|
|
|
Hi all.
I would like to make from three only two rows.
I hope someone can helps me to put values which are not "null" to a new row.
This is the table which exisits:
Table "MACandDNS":
NAME | ETH0 | ETH1 |
hugo | 000011223333 | 000011223334 |
sassy | 000013323333 | 000211223334 |
jan | 985555111113 | <empty> |
kevin |
|
|
|
|
|
Maybe something like?
INSERT INTO MACandDNSnew
SELECT NAME, ETH0 AS ETH
FROM firsttable
WHERE ETH0 IS NOT NULL
UNION
SELECT NAME, ETH1 AS ETH
FROM firsttable
WHERE ETH2 IS NOT NULL
|
|
|
|
|
This is a bit ugly, but it should work. Atleast if I have understood you correct.
SELECT NAME,ETH0 as ETH
FROM MACandDNS
WHERE ETH0 IS NOT NULL
AND ETH1 IS NOT NULL
UNION
SELECT NAME,ETH1 as ETH
FROM MACandDNS
WHERE ETH0 IS NOT NULL
AND ETH1 IS NOT NULL
Note that if ETH0 and ETH1 is the same for one name then you get only one row.
If that's a problem you can use UNION ALL but then the rows for certain won't be unique
"When did ignorance become a point of view" - Dilbert
|
|
|
|
|
Thank you all,
it helped me really much 
|
|
|
|
|
Hi!
I've to insert a value to a column in a particular data base table on a Button Click Event. I write the following statement to execute this:
SQLdb.Query("INSERT INTO Current(TeamID) values('countryId')");
countryId is an int value. I've printed and verified it. But when the click event happens and if I open the database and check for the corresponding column, only "countryId" is present(variable's name not the variable value as I expect). If I give countryId without single quotes in the statement also the result is the same. What to do to store the variable's value in to the database?
|
|
|
|
|
Preferably using parameterised queries, but as I don't know what SQLdb is, I cant show you what code to use. However the SQL query would look like
INSERT INTO Current(TeamID) values (@countryId)
|
|
|
|
|
Hi!
I'm using Sqlite. I'm calling Sqlite from C++.
|
|
|
|
|
Try:
SQLdb.Query("INSERT INTO Current(TeamID) values(' + countryId + ')");
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Now + countryId + is store in the table.
|
|
|
|
|
Oops - sorry, should have been:
SQLdb.Query("INSERT INTO Current(TeamID) values(" + countryId + ")");
Couldn't you have figured that out on your own?
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
Sorry! I'm a C++ Programmer. I'm new to SQL. That's why I'm troubling you again and again.
This Query is also not working. C++ shows error:
C2110:Can not add two pointers.
modified on Friday, July 30, 2010 7:23 AM
|
|
|
|
|
That's as close as I can get you. I haven't used C++ for years but I'm pretty sure you could create a string object with the id inserted and pass that to the query. You must know how to do that, surely. printf or sprintf, if I recall.
"If you think it's expensive to hire a professional to do the job, wait until you hire an amateur." Red Adair.
nils illegitimus carborundum
me, me, me
|
|
|
|
|
T.RATHA KRISHNAN wrote: Sorry! I'm a C++ Programmer. I'm new to SQL
Still, you cant destinguish between a string literal and a variable?
|
|
|
|
|
I suggest you tell your boss to hire an SQL specialist.
|
|
|
|
|
below is the stored procedure I’m having problems with. Its purpose is to filter the results within a result set returned by the spGetTicketList stored procedure, which works as expected.
If I remove the entire WHERE cluse section from the stored procedure I get 458545 rows returned, likewise if I include the Ticket Number range filters.
If I then add the Date range filter I get 446726 rows returned and if I include all the range filters I get 415179 rows returned. I am not providing any values for the parameters, other than the default values.
With all the filters in place there is 43,366 rows missing.
Any ideas?
ALTER PROCEDURE [dbo].[spFilterTicketList]
(
@LowTicketNumber VarChar(MAX) = '',
@HighTicketNumber VarChar(MAX) = 'zzzzzzzzzzzzzzzzzzzzz',
@LowDate DateTime = null,
@HighDate DateTime = null,
@AccountNumber varchar(MAX) = '%',
@AcccountName varchar(MAX) = '%',
@ShortName varchar(MAX) = '%',
@StoreId varchar(MAX) = '%',
@RoundId varchar(MAX) = '%'
)
AS
BEGIN
DECLARE @t1 as Table
(
Id bigint,
[Ticket Number] varchar(50),
[Account Number] varchar(50),
[Account Name] varchar(120),
[Short Name] varchar(50),
[Store Id] varchar(50),
[Date] DateTime,
[Round Id] varchar(50),
Source tinyint
);
INSERT @t1 EXEC spGetTicketList;
IF(@LowDate IS NULL)
BEGIN
SET @LowDate = CAST('1753-01-01 00:00:00.000' As DATE);
END
IF(@HighDate IS NULL)
BEGIN
SET @HighDate = CAST('9999-12-31 23:59:59.997' As DATE);
END
SELECT Id,
ISNULL([Ticket Number], '') AS [Ticket Number] ,
ISNULL([Account Number] , ' ') AS [Account Number],
ISNULL([Account Name] , ' ') AS [Account Name],
ISNULL([Short Name] , ' ') AS [Short Name],
ISNULL([Store Id] , ' ') AS [Store Id],
CAST(ISNULL([Date], CAST('1753-01-01 00:00:00.000' As DateTime)) As DateTime) AS [Date],
ISNULL([Round Id] , ' ') AS [Round Id],
[Source]
FROM @t1
WHERE ([Ticket Number] >= @LowTicketNumber)
AND ([Ticket Number] <= @HighTicketNumber)
AND (CAST([Date] AS DateTime) >= @LowDate)
AND (CAST([Date] AS DateTime) <= @HighDate)
AND ([Account Number] LIKE @AccountNumber)
AND ([Account Name] LIKE @AcccountName)
AND ([Short Name] LIKE @ShortName)
AND ([Store Id] LIKE @StoreId)
AND ([Round Id] LIKE @RoundId)
ORDER BY [Date] ASC ;
END
Steve Jowett
-------------------------
Real Programmers don't need comments -- the code is obvious.
|
|
|
|
|
Hi,
i have the table lab_inestigations which has created_by, processed_by and closed_by and all three columns are integer holding a user id (systm_user_id) from a table sysem_users but of course each created_by, processed_by and closed_by will have different systm_user_id value.. How can I make such sql statement to rertive the system_user_name for all three columns from system_users?
Thanks,
Jassim
|
|
|
|
|
You need to make 3 joins to the user table, one for each of your user fields in lab_inestigations, something like
SELECT *
FROM lab_inestigations I
INNER JOIN sysem_users S1 ON SI.UserID = I.CreatedBy
INNER JOIN sysem_users S2 ON S2.UserID = I.ProcessedBy
INNER JOIN sysem_users S3 ON S3.UserID = I.ClosedBy
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
I Have Table with columns as 1,2,3,4,5
I need to write query like
SELECT * FROM Table where columnName = 2. How can I specify column header in where clause
|
|
|
|
|
Hope this will help you
SELECT *<br />
FROM information_schema.columns<br />
WHERE COLUMN_NAME='YourColumnName'
I Love T-SQL
"Don't torture yourself,let the life to do it for you."
If my post helps you kindly save my time by voting my post.
www.aktualiteti.com
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
