|
There is a badly designed Caché database I have to grab data from via ODBC. Rather than updating a table, they decided to just append the new records. (Won't they be surprised, eventually. :p)
Anyway, is there a simple-ish way to get just the latest row for each set of records? Some sort of cool groupy join or something?
"ID" is an autoincrememnt int
Col2 is a varchar that identifies the set of records
Col3 is the data I'm ultimately interested in.
(There are other columns, of course, but probably irrelevant for this question...I hope)
Looks sorta like this:
ID Col2 Col3
1 A 23
2 A 23
3 A 23
4 A 24
5 B 17
6 B 18
7 B 19
8 C 01
9 C 01
What I want to end up with is
4 A 24
7 B 19
9 C 01
|
|
|
|
|
This view might work
create or replace view grouped_table as
select col2
,max(ID)
from table
group by col2
;
Then you query the table and join on the view
select t.ID
,t.col2
,t.col3
from table t
,grouped_table gt
where t.id = gt.id
This is just off the top of my head, though. 
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Ah...
Probably should I said I don't own the database, can't create anything on it, etc. Not even a temporary table/view. 
|
|
|
|
|
In that case try this then
select t.ID
,t.col2
,t.col3
from table t
,( select col2
,max(ID) id
from table
group by col2
) gt
where t.id = gt.id
Again, off the top of my head.
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]
posting about Crystal Reports here is like discussing gay marriage on a catholic church’s website.[Nishant Sivakumar]
|
|
|
|
|
Looks like it's working. Thanks.
Won't know for sure, though, until it finishes...if it does.
Started it a little over three hours ago and it began delivering the goods about two hours after that.
Oh...did I forget to mention this $%^$@#! table has over 177 million rows in it? And I expect the result to be in the neighborhood of 3 million?
Hopefully, it will finish before I go home this afternoon. Otherwise, pfft! "Night Watchman" will come along at 3 a.m. and shut off my computer and I'll have to start all over again in the morning. 
EDIT:
It finished. And, as soon as I clicked on the result to try to copy it, BOOM! 'So sorry. We've run out of virtual memory and have to close. Hope you weren't working on anything too terribly important'.
Or words to that effect.
|
|
|
|
|
Using this table:
create table tt (
id int,
Col2 varchar(1),
Col3 Int)
This seems to work for me ...
select * from TT where id in (
select max(id) from tt group by col2)
|
|
|
|
|
If you are doing an inner join does it matter which order the tables are in?
Is
Table1 inner join Table2
any different from
Table2 inner join Table1
Could there be any difference?
Brent
|
|
|
|
|
No, there cannot be. It just means rows between the two tables with the same value in the 'joined' columns. So the order in which the table names are mentioned doesn't matter.
However, for Right and Left joins, it does matter.
|
|
|
|
|
Hi all.
I'm now developing a website that connect to Postgre SQL database. I used NpgSQL.DLL to connect from C# to database. I also finished testing this project in my local pc, it worked well. But unfortunately it did not work well in server. Some errors in connecting to server happened.
These errors happened when website had much people logged in and began using database query functions ( select, update, delete .. ). I think errors begin from codes in connect database functions. but i still can not fix it. this is my connecting database codes.
------ codes connects database ---------
-- Connect.cs file.
public static void connectDB()
{
try
{
conn.ConnectionString = connection;
conn.Open();
}
catch
{
}
}
public static void closeConnectDB()
{
conn.Close();
}
public static int ExecuteQuery(string SQL)
{
try
{
connectDB();
NpgsqlCommand cmd = new NpgsqlCommand(SQL, conn);
return cmd.ExecuteNonQuery();
}
catch
{
closeConnectDB();
return 0;
}
}
public static DataSet SelectQuery(string SQL)
{
DataSet ds = new DataSet();
try
{
connectDB();
NpgsqlDataAdapter nDa = new NpgsqlDataAdapter(SQL, conn);
nDa.Fill(ds);
return ds;
}
catch
{
closeConnectDB();
return ds;
}
}
----------------- use to execute these codes --------
....
--- calling functions files.
try
{
string strTest = "Select * from abc where .. ";
DataSet ds1 = new DataSet();
ds1 = Connect.SelectQuery(strTest);
if (ds1.Tables[0].Rows.Count > 0)
{
}
else
{
int kq = Connect.ExecuteQuery(strInsert);
if (kq > 0)
{
int kk = Connect.ExecuteQuery(strUpdate);
kk = Connect.ExecuteQuery(strUpdate2);
strHTML = "OK";
}
}
}
catch
{
strHTML = "errors.";
}
finally
{
int kk = Connect.ExecuteQuery(strUpdate);
kk = Connect.ExecuteQuery(strUpdate2);
strHTML += "-- Sub Finally";
}
-----------------End calling function---------------
Here sometimes it worked well but sometimes catched errors.
Can you help me? thanks and regards.
|
|
|
|
|
Looking at your code, I can't help but think you're being hammered because you have static methods in a multi user system, and the code isn't thread safe. In other words, you have static variables (take a look at your connection variable), and you are opening and closing them without any form of synchronisation in place. In other words, you could have two users both "open" a connection at the same time, and then one finishes and closes the connection before the other one completes.
The easiest fix is to move the open and close logic into the other methods, rather than relying on member variables.
|
|
|
|
|
thanks. i did it and my problems solved.
modified 23-Feb-12 2:43am.
|
|
|
|
|
stop your static abuse, and start using the using construct[^], it fits quite well in DB applications.
|
|
|
|
|
|
Hi everyone,
I want to make a library project,I have made this model and I have a question is this a correct desgin for implementation or no
Main table is Books I have these fields on it
Book ID Bookname Title publish year description ISBN
Authors table
Book ID author name phone address email
publishers table
BookID publisher name phone address email
BookID's are primary key in this databse and I am going to use of it for relationship
is this a correct design and do I need to insert anything?
Thanks
|
|
|
|
|
messages wrote: question is this a correct desgin for implementation or no
Instead of having each design validated here, you could use the normalization-rules and validate it yourself.
Bastard Programmer from Hell 
|
|
|
|
|
could you help me ,please I have read normalization-rules I want only know is this correct or it has problem(I mean is your sentence).I have almost like this problem I want to know do I need to normalization-rules no?
Of one Essence is the human race
thus has Creation put the base
One Limb impacted is sufficient
For all Others to feel the Mace
(Saadi )
|
|
|
|
|
Hamid. wrote: I have almost like this problem I want to know do I need to normalization-rules no?
If you want a decent relational model, then it would be wise to normalize them, at least up to 3NF. You can "prove" that your model is in 1NF by checking the five conditions mentioned on the Wikipedia[^].
I know that you're looking for a yes/no answer, but that's not good enough. If things go wrong you're not helped much by shouting that "Eddy" told you that your model is right/wrong. You want to be able to defend your model, as opposed to taking the word of a stranger.
Bastard Programmer from Hell
|
|
|
|
|
I don't think "book ID" is a good name for the primary key of an authors table.
|
|
|
|
|
Yeah I think ISBN is a good idea.
|
|
|
|
|
Your structure is quite incorrect. Think about 1 to many and ask these questions.
Does a publisher publish only 1 book?
Does and Author ALWAYS only write 1 book?
Does a book ALWAYS have only 1 author.
Go follow Eddies suggestion, it will help you understand the basics.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your answer I have read wikipedia 1NF,....but how can I those rules in my project, I need to have a clear idea to write let me explain in this way
(1)we have some publishers
(2)we have some authors
publishers publish different books of different authors so we need to three tables(publisher,author and book) I guess that they should have a many to many relationship is it correct?
could you tell me please how can I implement relationships between these tables?
Thanks
|
|
|
|
|
Now you are getting the idea.
You need Link tables
lnkPublisherBook
LinkID
BookID
PublisherID
lnkAuthorBook
LinkID
BookID
AuthorID
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Thanks for your help may I ask my questions in the future?
|
|
|
|
|
Never stop asking questions, just make sure you have thought about the questions you are asking. ALWAYS do some research before dumping it into the forum, especially as you are learning. Almost every question you can come up with will have already been asked many times.
Never underestimate the power of human stupidity
RAH
|
|
|
|
|
Hi again
I have made this schema,is this correct(its my first question)
LogIn table
user_name(primary key) password AuthorID(Foreign key) PublisherID(foreign key)
Publishers table
publisherID(primark key) publisher name phone address (and other fields)
Authors table
AuthorID(primary key) Author name phone address publisherID(Foreign key) (and other fields)
Books Table
BookID(primary key) Book name Year Title (and other fields)
and its my second question I thought to make this table for AuthorBook
AuthorBook table
AuthorBookID(primary key) AuthorID(Foreign key) BookID(Foreign key)
Do I need to Foreign key in Authors table or Book tables?
Thanks
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
